CODEWITHSUNDEEP
sqlsql-servert-sql
user9386933
user9386933

Reputation:

select top 5 from group

I have this list of transactions ordered by the client# then transaction date. What I would like to is have a list just like this except only pick the first 5 transactions per client#. If anyone can tell me how to do this I'd really appreciate it!

Transaction list example

I'm using sql server 2012

Upvotes: 0

Views: 87

Answers (3)

Derrick Moeller
Derrick Moeller

Reputation: 4950

An alternative option using CROSS APPLY.

SELECT DISTINCT yt.client_no, ca.Last_Mdt, ca.price, ca.Tax_1, ca.Cashier
FROM YourTable yt
CROSS APPLY (SELECT TOP 5 *
             FROM YourTable
             WHERE client_no = yt.client_no
             ORDER BY Last_Mdt ASC) ca

Alternatively if you have a client table as well. Where presumably client_no is already unique.

SELECT yct.client_no, ca.Last_Mdt, ca.price, ca.Tax_1, ca.Cashier
FROM YourClientTable yct
CROSS APPLY (SELECT TOP 5 *
             FROM YourTable
             WHERE client_no = yt.client_no
             ORDER BY Last_Mdt ASC) ca

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2011

We can also use cross/outer apply operator for this kind of stuff. You can choose cross / outer apply and can do order by as per your requirement.

SELECT k.* 
FROM Table a
OUTER APPLY
(
    SELECT TOP 5 *
    FROM Table b
    WHERE a.client_no = b.client_no
    ORDER BY b.Last_Mdt ASC 
)k

Upvotes: 0

Prabhath Amaradasa
Prabhath Amaradasa

Reputation: 503

You can use Window Function Dense_RANK()

SELECT
 dt.client_no, dt.Last_Mdt, dt.price, dt.Tax_1, dt.Cashier
FROM(
SELECT client_no, Last_Mdt, price, Tax_1, Cashier,
      DENSE_RANK () OVER       (PARTITION BY client_no ORDER BY Last_Mdt ASC) AS Rank  
 FROM  Table
)dt 
WHERE dt.Rank <=5

Upvotes: 1

Related Questions