Is it possible to nest SELECT statements?

Hi Jingyang Li,

Thanks for the last piece of code. It works perfectly. However, I don’t have the faintest idea how it works.

I’ve read the article on http://ift.tt/LyDqFS, about ROW_NUMBER function, but I don’t understand what is actually for, because they used some example form AdventureWorks, that I cannot understand.

However, from your code I have some understanding of that function. If I understood correctly, mycte is alias for temporary data table. It contains all the rows from the table plus one column RN40 with row numbers, and that table is sorted backwards.

On the second part of the query, from mycte you select those rows where RN40 is bigger than 20 and smaller or equal than 40, and then you sort it ascending.

If I got it right, then I have to ask again about the performance. Even in small biz I work in, we have about 20000 orders yearly. If I pull all of them so I could select 20 of them, what I really do?

In contrast to this, could you tell me what’s wrong with this:

Select top 20 * from (
Select top 40 * from dbo.Orders order by OrderID desc) as A
Order by OrderID asc

Click on display estimated execution plan button in SSMS, and see for yourself.