How to use order by clause in derived table sql server 2008

Question

How to use order by clause in derived table sql server 2008

kuppurajm

SSCommitted

Points: 1593
More actions
March 17, 2011 at 12:14 am

#248443

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software