March 17, 2011 at 12:14 am
Hi,
How to use order by clause in derived table sql server 2008?
My query is
SELECT * FROM
(
select RegionID,RegionCode,RegionName,Active,
ROW_NUMBER() OVER (ORDER BY RegionID ) AS RowNumber,count(*) over() as MaxNum
from RegionMaster
)as Pagedrecords
WHERE RowNumber BETWEEN (@StartIndex+1) AND @EndIndex
ORDER BY RegionCode
This Works fine.But I need to use order by clause in inner query like
March 17, 2011 at 6:08 am
No reason why you can't move that into the derived table. I've done it before.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2011 at 6:20 am
Order by is only permitted in the outer-most select statement unless there is a TOP. Order by on a subquery is only honoured if there is a row-limiting TOP (not top 100%) and then only for the purposes of calculating the TOP, not necessarily for the display order of the returned resultset.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2011 at 6:24 am
GilaMonster (3/17/2011)
Order by is only permitted in the outer-most select statement unless there is a TOP. Order by on a subquery is only honoured if there is a row-limiting TOP (not top 100%) and then only for the purposes of calculating the TOP, not necessarily for the display order of the returned resultset.
Oh crud, I wasn't thinking (again). Right, you have to have a TOP operation too. Nuts. Sorry. Thanks for the correction Gail (again).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply