How to return top 3 rows by DATE for each value in a column in SQL Server?

Question

My table looks like this:

SERIAL   COMPARTMENT      VALUE    DATE
A        DIFF FRONT       0        1/1/2020
A        TRANSMISSION     5        1/1/2020
B        DIFF FRONT       1        1/2/2020
B        TRANSMISSION     1        1/2/2020
A        DIFF REAR        0        1/3/2020
A        DIFF REAR        2        1/4/2020
A        DIFF FRONT       3        1/5/2020

I am trying to get the three most recent rows by DATE for each SERIAL where COMPARTMENT is DIFF REAR or DIFF FRONT. Should look like this:

SERIAL     COMPARTMENT      VALUE     DATE
B          DIFF FRONT       1         1/2/2020
A          DIFF REAR        0         1/3/2020
A          DIFF REAR        2         1/4/2020
A          DIFF FRONT       3         1/5/2020

I tried:

SELECT TOP 3 *
FROM table
WHERE COMPARTMENT = 'DIFF REAR' or COMPARTMENT = 'DIFF FRONT'

Obviously this did not do what I wanted. I am hoping I can get some help on the matter. Thanks in advance.

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) If not, why not? — Thom A, Oct 02 '20 at 16:26

score 0 · Accepted Answer · answered Oct 02 '20 at 16:27

You can do this with APPLY/Lateral JOIN:

SELECT t.*
FROM (SELECT DISTINCT Serial FROM [table]) serials
OUTER APPLY (
    SELECT TOP 3 *
    FROM [table] t0
    WHERE COMPARTMENT IN ('DIFF REAR', 'DIFF FRONT')
        AND t0.Serial = serials.Serial
    ORDER BY Date DESC
) t

Or a Windowing function

SELECT * 
FROM (
    SELECT *
        , row_number() OVER (PATITION BY Serial ORDER BY Serial, Date DESC) As rn
    FROM [table]
    WHERE COMPARTMENT IN ('DIFF REAR', 'DIFF FRONT')
) t
WHERE t.rn <= 3

How to return top 3 rows by DATE for each value in a column in SQL Server?

1 Answers1