SQL Request where I get the lastest result from a date

Question

I want to get the latest datetime from a table but also joining another table.

This is my first table :

id	title
1	a
2	ac
3	at

And this is my second table :

id	content	author	date	fk_term
202	Maecenas ut massa ...	Renee Copnar	2022-08-17	1
351	Sociis natoque penatibus...	Rhona Levesque	2022-10-30	1
557	Phasellus in felis...	Kissie Blabber	2022-09-11	1
383	Maecenas tristique...	Saunders Robet	2022-08-18	2
429	In sagittis dui...	Jorey Strodder	2022-12-21	2
655	Maecenas ut massa...	Sim Harrod	2023-01-07	2

And also I only want one result per first table id Like this :

content	author	date	fk_term	title
Sociis natoque penatibus...	Rhona Levesque	2022-10-30	1	a
Maecenas ut massa...	Sim Harrod	2023-01-07	2	ac

@nbk Maybe I'm missing something here, but there is no requirement for a pivot. That duplicate feels incorrect. — JNevill, Feb 23 '23 at 14:44
@jarlh, I believe the ask here is to get the newest record for each `fk_term`, specifically for `fk_term=1`. Something like `SELECT * FROM (SELECT st.*, ROW_NUMBER() OVER(PARTITION BY fk_term ORDER BY date DESC) as rn FROM second_table) st WHERE rn=1;` or what-have-you. — JNevill, Feb 23 '23 at 14:44
@JNevill the link has everything needed topivot static or dynamic, so it fits perfectly — nbk, Feb 23 '23 at 14:46
@nbk It's a great link for pivot, however a pivot isn't the right solution to OPs question, so it's inappropriate. Instead maybe: https://stackoverflow.com/questions/6011052/selecting-rows-with-the-highest-date or https://stackoverflow.com/questions/17038193/select-row-with-most-recent-date-per-user — JNevill, Feb 23 '23 at 14:47

score -1 · Answer 1 · answered Feb 23 '23 at 14:56

As one option: first get the latest record for each fk_term:

SELECT * 
FROM second_table st 
WHERE date = 
    (
        SELECT max(date) 
        FROM second_table
        WHERE st.fk_term = fk_term
    )

Then use that as a subquery to join:

 SELECT st2.content, st2.author, st2.date, st2.fk_term, ft.title
 FROM first_table ft
     INNER JOIN 
     (
        SELECT * 
        FROM second_table st 
        WHERE date = 
            (
                SELECT max(date) 
                FROM second_table
                WHERE st.fk_term = fk_term
            )
     ) st2

SQL Request where I get the lastest result from a date

1 Answers1