CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
Nullqwerty
Nullqwerty

Reputation: 1168

Selecting 5 Most Recent Records Of Each Group

The below statement retrieves the top 2 records within each group in SQL Server. It works correctly, however as you can see it doesn't scale at all. I mean that if I wanted to retrieve the top 5 or 10 records instead of just 2, you can see how this query statement would grow very quickly.

How can I convert this query into something that returns the same records, but that I can quickly change it to return the top 5 or 10 records within each group instead, rather than just 2? (i.e. I want to just tell it to return the top 5 within each group, rather than having 5 unions as the below format would require)

Thanks!

WITH tSub 
     as (SELECT CustomerID, 
                TransactionTypeID, 
                Max(EventDate)     as EventDate, 
                Max(TransactionID) as TransactionID 
         FROM   Transactions 
         WHERE  ParentTransactionID is NULL 
         Group  By CustomerID, 
                   TransactionTypeID) 

SELECT * 
from   tSub 
UNION 
SELECT t.CustomerID, 
       t.TransactionTypeID, 
       Max(t.EventDate)     as EventDate, 
       Max(t.TransactionID) as TransactionID 
FROM   Transactions t 
WHERE  t.TransactionID NOT IN (SELECT tSub.TransactionID 
                               FROM   tSub) 
       and ParentTransactionID is NULL 
Group  By CustomerID, 
          TransactionTypeID

Upvotes: 0

Views: 466

Answers (1)

Ranjit Singh
Ranjit Singh

Reputation: 3735

Use Partition by to solve this type problem

select values from
(select values ROW_NUMBER() over (PARTITION by <GroupColumn> order by <OrderColumn>) 
as rownum from YourTable) ut where ut.rownum<=5

This will partitioned the result on the column you wanted order by EventDate Column then then select those entry having rownum<=5. Now you can change this value 5 to get the top n recent entry of each group.

Upvotes: 2

Related Questions