CODEWITHSUNDEEP
sqlsql-server
Sean T
Sean T

Reputation: 2504

SQL Getting Top 2 Results for each individual column value

I have a table 'Cashup_Till' that records all data on what a particular till has recorded in a venue for a given day, each venue has multiple tills all with a designated number 'Till_No'. I need to get the previous 2 days entries for each till number. For each till Individually I can do this...

SELECT TOP 2 T.* FROM CashUp_Till T 
WHERE T.Till_No = (Enter Till Number Here)
ORDER BY T.Till_Id DESC

Some venues have 20-30 tills so Ideally I need to do all the tills in one call. I can pass in a user defined table type of till numbers, then select them in a subquery, but that's as far as my SQL knowledge takes me, does anyone have a solution?

Upvotes: 1

Views: 309

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Here is one way:

SELECT T.*
FROM (SELECT T.*,
             ROW_NUMBER() OVER (PARTITION BY Till_No ORDER BY Till_Id DESC) as seqnum
      FROM CashUp_Till T 
     ) T
WHERE seqnum <= 2;

This assumes that there is one record per day, which I believe is suggested by the question.

If you have a separate table of tills, then:

select ct.*
from t cross apply
     (select top 2 ct.*
      from cashup_till ct
      where ct.till_no = t.till_no
      order by till_id desc
     ) ct;

Upvotes: 2

Related Questions