How to create a complex query (inner join, sort by ID and time, cut list)?

[CobraKai]

Hello!



I am looking for help to find a solution for following problem:

I have 2 tables - 1st table contains data about goods (name, number, date of creation over years, parts are repeated many times with different date, different batch number and differnet classifcation like OK/NOK), 2nd table contains only a few goods with ID number.

Target:

List parts from 1st table which are in 2nd table but I should show only latest 3 date for each listed part (from 1st table).

List should look like this:

A - 28.06.2019
A - 27.06.2019
A - 26.06.2019
B - 28.06.2019
B - 25.06.2019
B - 24.06.2019
C - 25.06.2019
C - 23.06.2019
C - 21.06.2019

How should I start with it? I can join tables order them by ID number but I can't select only 3 pcs from each part number.

Thanks for help.

[CJ_London]

you'll need to use a subquery to join on date

the query might be something like

SELECT *
FROM tbl1
where tbl1.crDate=(SELECT TOP 3 crDate FROM tbl1 D WHERE partno=tbl1.partno ORDER BY crDate DESC)

note the D alias in the subquery