CODEWITHSUNDEEP
t-sqlssms
jenhil34
jenhil34

Reputation: 1501

How to select the last 5 records per group

I am doing a query that looks at each part transaction within a certain time period. Depending on the part, it could have maybe just 1 transaction or upwards of 1000 transactions. I need to only pull the last 5 transactions per part. Here is my query right without selecting only the last 5 transactions per part.

 SELECT     partnum, trandate, extcost
 FROM       parttran
 WHERE      trandate between '2012-05-15' and '2013-05-14'

I greatly appreciate your help!

Upvotes: 1

Views: 76

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

Always useful in these situations: a CTE with a window function like ROW_NUMBER:

WITH CTE AS
(
    SELECT  partnum, trandate, extcost,
            rn = ROW_NUMBER() OVER (PARTITION BY partnum ORDER BY trandate DESC)
    FROM    parttran
    WHERE   trandate between '2012-05-15' and '2013-05-14'
)
SELECT partnum, trandate, extcost FROM CTE WHERE rn <= 5

Upvotes: 2

Related Questions