1
SET @LatestRowNum = 2; 
WITH OrderedOrders AS  
( 
    SELECT 
        ROW_NUMBER() OVER(order by ID desc) AS Rownum, 
        *
    FROM  someTable WITH(NOLOCK)                
    ORDER BY ID DESC
)
SELECT *
FROM
OrderedOrders WHERE Rownum = @LatestRowNum

The above query throwing me

Msg 1033, Level 15, State 1, Line 17 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

How to achieve the objective whereby sometimes I'm expecting the last records or sometimes the second last records?

Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
SuicideSheep
  • 5,260
  • 19
  • 64
  • 117

2 Answers2

2

You don't need inner ORDER clause since you are using ORDER clause in ROW_NUMBER().

Try this

SET @LatestRowNum = 2; 
WITH OrderedOrders AS  
( 
    SELECT ROW_NUMBER() OVER(order by ID desc) AS Rownum,*
    FROM someTable WITH(NOLOCK)                
)
SELECT * FROM
OrderedOrders WHERE Rownum = @LatestRowNum
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • this is not always true. please refer `General Remark` in following MSDN link : https://msdn.microsoft.com/en-in/library/ms186734.aspx – par Oct 13 '16 at 09:32
  • @par In this situation the extra order won't change a thing however, since it's on the same value. – Jens Oct 13 '16 at 09:37
0

Though ordering is not needed other than in row_number if only one record returned by the query. but You can try adding TOP keyword. Note that TOP 100 PERCENT does not work. you can try adding TOP 9999999 (maximum) possible number.
Also some comment in OP suggests that No need to add order by because of ORDER BY in ROW_NUMBER but ROW_NUMBER does not order rows always (reference : https://msdn.microsoft.com/en-in/library/ms186734.aspx. check General Remarks).

There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution

You can refer couple of helpful articles:
http://blog.sqlauthority.com/2010/08/23/sql-server-order-by-does-not-work-limitation-of-the-views-part-1/

Create a view with ORDER BY clause

Edit:
other solution is use a variable instead of constant in TOP keyword. for example TOP (@T) PERCENT. 

declare @t as tinyint  = 100
declare @tbl as table (num1 int,num2 int)
insert into @tbl values (7,1),(2,7),(4,6),(6,4), (7,1),(2,7),(4,6),(6,4)
select * from(select top 100 percent * from @tbl order by num1) t 
select * from(select top (@t) percent * from @tbl order by num1) t

In above example the last query gives expected result. If there is a variable in TOP keyword, the SQL assumes there could be any value so it will always consider it instead of ignoring it.

Community
  • 1
  • 1
par
  • 1,061
  • 1
  • 11
  • 29
  • `TOP 100 PERCENT` works in SQL Server and I would never recommend adding `TOP largest_number_ever`. And, it's true that the `ORDER BY` in `ROW_NUMBER()` does not guarantee the rows will be returned in sorted order, but the user is only trying to return a single row based on the value of `ROW_NUMBER()` (in which case sorting is pointless), because `ROW_NUMBER()` is unique (if you're not using `PARTITION BY`) – Radu Gheorghiu Oct 13 '16 at 09:31
  • @RaduGheorghiu : Sorting is important as I'm only expecting either the latest record, or second latest record. If some old data being sorted as first row then clearly it didnt meet what I want – SuicideSheep Oct 13 '16 at 09:37
  • @RaduGheorghiu try below code and check execution plan. there would not be sorting in plan. insert into tbl1 values (7,1),(2,7),(4,6),(6,4) create view vTmp as select top 100 percent * from tbl1 order by num1; **Or** declare `@tbl` as table (num1 int,num2 int) insert into `@tbl` values (7,1),(2,7),(4,6),(6,4), (7,1),(2,7),(4,6),(6,4) select * from(select top 100 percent * from `@tbl` order by num1) t – par Oct 13 '16 at 09:46