Tuesday, December 10, 2013

Get all records before the last inserted record in SQL Server (TSQL)

In a scenario where you want to show all records before the latest injected record, one solution would be to get the latest date or perhaps tha latest primary key field. But, in cases where there is no primary key defined, there's another solution using Row_Number() in SQL Server. See the two queries below:
 -- option 1  
 select Production.Product.ProductID, Name as ID from Production.Product   
  where (ProductID < (Select MAX(ProductID) from Production.Product))  
  order by ProductID desc;
-- option 2 WITH ProductsView AS ( SELECT ProductID, Name, ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNumber From Production.Product ) SELECT ProductID, Name FROM ProductsView WHERE (RowNumber < (select Max(RowNumber) from ProductsView)) order by ProductID desc;
Reference: Row_Number() in TSQL


Post a Comment