SQL Server - select the top 2 rows

Question

I am trying to write a query that will return

• The most recent AccountDate with a record of 0 on the location identifier
• Then the second most recent account for each location identifier. The entry can be 1 or 0.
• If there are two accounts with the same date, then return the last AccountDate based on DateAccountLoaded

Be that as it may, my solution does not look very elegant. Has anyone got a better way to achieve this.

See below my solution

CREATE TABLE [dbo].[TopTwoKeyed]( ID INT IDENTITY(1,1) PRIMARY KEY(ID), [LocationID] [int] NULL, [AccountDate] [date] NULL, [Record] [tinyint] NULL, [DateAccountLoaded] [date] NULL ) INSERT INTO [dbo].[TopTwoKeyed] ( [LocationID], AccountDate, Record, DateAccountLoaded ) VALUES(1,'2009-10-31',0,'2011-03-23'), (1,'2008-10-31',1,'2011-03-23'), (1,'2008-10-31',0,'2010-03-22'), (1,'2008-10-31',1,'2009-03-23'), (1,'2011-10-31',1,'2010-03-22'), (1,'2009-10-31',0,'2010-03-23'), (2,'2011-10-31',0,'2010-03-23'), (2,'2010-10-31',0,'2010-03-23'), (2,'2010-10-31',1,'2010-03-23'), (2,'2010-10-31',1,'2009-03-23'), (3,'2010-10-31',0,'2010-03-23'), (3,'2009-10-31',0,'2010-03-23'), (3,'2008-10-31',1,'2010-03-23') -- Get the most recent Account Date per locationID which has a record type of 0 SELECT f.LocationID ,f.AccountDate ,f.DateAccountLoaded FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY AccountDate DESC,DateAccountLoaded DESC) AS RowNumber ,LocationID AS LocationID ,AccountDate AS AccountDate ,DateAccountLoaded AS DateAccountLoaded FROM [dbo].[TopTwoKeyed] WHERE Record = 0 ) f WHERE f.RowNumber = 1 UNION ALL SELECT ff.LocationID ,ff.AccountDate ,ff.DateAccountLoaded FROM ( -- Get the SECOND most recent AccountDate. Can be either Record 0 or 1. SELECT ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY AccountDate DESC,DateAccountLoaded DESC) AS RowNumber ,LocationID AS LocationID ,AccountDate AS AccountDate ,DateAccountLoaded 'DateAccountLoaded' FROM [dbo].[TopTwoKeyed] tt WHERE EXISTS ( -- Same query as top of UNION. Get the most recent Account Date per locationID which has a record type of 0 SELECT 1 FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY AccountDate DESC,DateAccountLoaded DESC) AS RowNumber ,LocationID AS LocationID ,AccountDate AS AccountDate FROM [dbo].[TopTwoKeyed] WHERE Record = 0 ) f WHERE f.RowNumber = 1 AND tt.LocationID = f.LocationID AND tt.AccountDate < f.AccountDate ) ) ff WHERE ff.RowNumber = 1 -- DROP TABLE [dbo].[TopTwoKeyed] 

Answer 1

You can use the row_number subquery to find the most recent account date. Then you can outer apply find the following most recent account date:

 select MostRecent.LocationID , MostRecent.AccountDate , SecondRecent.AccountDate from ( select row_number() over (partition by LocationID order by AccountDate desc, DateAccountLoaded desc) as rn , * from TopTwoKeyed where Record = 0 ) MostRecent outer apply ( select top 1 * from TopTwoKeyed where Record in (0,1) and LocationID = MostRecent.LocationID and AccountDate < MostRecent.AccountDate order by AccountDate desc , DateAccountLoaded desc ) SecondRecent where MostRecent.rn = 1 

---

EDIT: To position the lines below eachother, you may have to use union . One row_number cannot work because the second row has different criteria for the Record column.

 ; with Rec0 as ( select ROW_NUMBER() over (partition by LocationID order by AccountDate desc, DateAccountLoaded desc) as rn , * from TopTwoKeyed where Record = 0 ) , Rec01 as ( select ROW_NUMBER() over (partition by LocationID order by AccountDate desc, DateAccountLoaded desc) as rn , * from TopTwoKeyed t1 where Record in (0,1) and not exists ( select * from Rec0 t2 where t2.rn = 1 and t1.LocationID = t2.LocationID and t2.AccountDate < t1.AccountDate ) ) select * from Rec0 where rn = 1 union all select * from Rec01 where rn = 1