CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Luqman
Luqman

Reputation: 163

How to take n top records in order by query

I am using following query to retrieve transactions but I need only top 2 records for each user

SELECT  bt.FromID, bt.ToID, u.Username, bt.Amount, bt.ToOpeningBal, bt.ToClosingBal, bt.CreatedDate AS TransferDate, u.CreatedDate AS RETLCreatedDate
FROM   BalanceTransfer AS bt INNER JOIN Users AS u ON bt.ToID = u.ID
WHERE  u.CreatedDate BETWEEN '2017-01-04 00:00:14.1400000' AND '2017-01-10 00:00:14.1400000'
AND ( bt.ToOpeningbal = 0 or bt.ToOpeningbal = 200)
ORDER BY bt.CreatedDate



Username  | Amount |    ToOpeningBal|   ToClosingBal |  TransferDate
----------|--------|----------------|----------------|----------------
ABC       |   2044 |   0            |  2044          | 2/18/17 11:00 AM
ABC       |   2000 |   0            |  2000          | 2/18/17 11:28 AM
ABC       |   5110 |  200           |  5310          | 2/20/17 4:52 PM
ABC       |   3066 |   0            |  3066          | 2/18/17 12:48 PM
ABC       |   3069 |   0            |  3069          | 2/21/17 7:38 PM
XYZ       |   2050 |   0            |  2050          | 2/18/17 2:23 PM
XYZ       |   2050 |   0            |  2050          | 2/18/17 3:00 PM
XYZ       |   2044 |   0            |  2044          | 2/18/17 12:34 PM
XYZ       |   5125 |   0            |  5125          | 2/18/17 8:39 PM

For above result I need only top 2 records for each username

Upvotes: 0

Views: 55

Answers (1)

B.Porter
B.Porter

Reputation: 367

If using SQL Server, add a row_number and filter using that:

SELECT * FROM (
SELECT  
    bt.FromID, 
    bt.ToID, 
    u.Username, 
    bt.Amount, 
    bt.ToOpeningBal, 
    bt.ToClosingBal, 
    bt.CreatedDate AS TransferDate, 
    u.CreatedDate AS RETLCreatedDate,
    ROW_NUMBER() OVER(PARTITION BY u.Username ORDER BY bt.CreatedDate) rowNum
FROM   BalanceTransfer AS bt 
   INNER JOIN Users AS u ON bt.ToID = u.ID
WHERE u.CreatedDate BETWEEN '2017-01-04 00:00:14.1400000' AND '2017-01-10 00:00:14.1400000'
    AND (bt.ToOpeningbal = 0 or bt.ToOpeningbal = 200)
ORDER BY bt.CreatedDate) a
WHERE rowNum <=2

Upvotes: 1

Related Questions