Select records from two tables based on the MAX of one column

Question

I have a query that I'm trying to get only one row to display for each volume and drawing_num based on the MAX of a date field. I'm posting my query below along with the results I'm getting plus what the results I'm intending to get. Any help is appreciated.

SELECT
    cc_qaqc_drawings.volume,
    cc_qaqc_drawings.drawing_num,
    cc_qaqc_drawings.drawing_name,
    MAX(cc_qaqc_drawings_detail.rev_date) AS revdate,
    cc_qaqc_drawings_detail.rev_desc,
    cc_qaqc_drawings_detail.rev_type,
    da.cc_qaqc_drawings.id
FROM da.cc_qaqc_drawings
LEFT JOIN da.cc_qaqc_drawings_detail 
  ON da.cc_qaqc_drawings.id = da.cc_qaqc_drawings_detail.draw_id
WHERE cc_qaqc_drawings.jobnumber = '2099'
GROUP BY
    cc_qaqc_drawings.volume,
    cc_qaqc_drawings.drawing_num,
    cc_qaqc_drawings.drawing_name,
    da.cc_qaqc_drawings.id,
    cc_qaqc_drawings_detail.rev_desc,
    cc_qaqc_drawings_detail.rev_type
ORDER BY
    cc_qaqc_drawings.volume,
    cc_qaqc_drawings.drawing_num,
    MAX(cc_qaqc_drawings_detail.rev_date) DESC;

Below are the results I'm getting with the query above.

Volume	DrawNum	DrawName	RevDate	RevDesc	RevType	ID
1	A1001	Windows	21-MAR-23	Glass	RFDC	2250
1	A1001	Windows	20-MAR-23	Glass	RFDC	2250
1	A1001	Windows	19-MAR-23	Glass	ASI	2250
1	A1001	Windows	18-MAR-23	Glass	RFI	2250
1	A4400	Frames	NULL	NULL	NULL	2244
1	A6100	Schedule	NULL	NULL	NULL	2245
9	A1099	Drawings	18-MAR-23	Classes	RFI	2261
9	A1099	Drawings	17-MAR-23	Classes	RFDC	2261

Below are the results I would like to get.

Volume	DrawNum	DrawName	RevDate	RevDesc	RevType	ID
1	A1001	Windows	21-MAR-23	Glass	RFDC	2250
1	A4400	Frames	NULL	NULL	NULL	2244
1	A6100	Schedule	NULL	NULL	NULL	2245
9	A1099	Drawings	18-MAR-23	Classes	RFI	2261

1) Please fix your query which currently doesn't even have a `select` keyword, 2) please show sample data, 3) consider this answer: https://stackoverflow.com/a/123481/1024832 — Marc, Mar 23 '23 at 16:25
Sorry, I didn't realize it posted already. I'm fixing it now. — house, Mar 23 '23 at 16:38
@Marc I've fixed it now. I guess I must have pressed the post button on accident. — house, Mar 23 '23 at 16:47
This question is pretty much answered at the link I posted in my first comment. — Marc, Mar 23 '23 at 17:13
Please prepend each column with the table name it belongs to. We can't really guess where each one belongs to. — The Impaler, Mar 23 '23 at 17:38

score 2 · Accepted Answer · answered Mar 23 '23 at 18:24

You can use ROW_NUMBER() to identify the rows you want. Then filtering is trivial.

For example:

select *
from (
  SELECT
    d.volume,
    d.drawing_num,
    d.drawing_name,
    t.rev_date,
    t.rev_desc,
    t.rev_type,
    d.id,
    row_number() over(partition by d.volume, d.drawing_num 
                      order by t.rev_date desc) as rn
  FROM da.cc_qaqc_drawings d
  LEFT JOIN da.cc_qaqc_drawings_detail t ON d.id = t.draw_id
  WHERE d.jobnumber = '2099'
) x
where rn = 1

Select records from two tables based on the MAX of one column

1 Answers1