SQL - select only latest record for each foreign key-CodePudding

Hello so my problem is basically, I have a table files like so:

foreign_key	string	created_at
1	"a"	2021-11-10
2	"b"	2021-11-15
2	"c"	2021-11-18

Now what I need is to select one record (string) for each distinct foreign_key and it always needs to be the latest (created_at)

I can't use subquery in selects because of speed.

The result should look like this:

foreign_key	string	created_at
1	"a"	2021-11-10
2	"c"	2021-11-18

CodePudding user response：

Using ROW_NUMBER:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY foreign_key
                                   ORDER BY created_at DESC) rn
    FROM yourTable t
)

SELECT foreign_key, string, created_at
FROM cte
WHERE rn = 1;

Another approach, using joins:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT foreign_key, MAX(created_at) AS max_created_at
    FROM yourTable
    GROUP BY foreign_key
) t2
    ON t2.foreign_key = t1.foreign_key AND
       t2.max_created_at = t1.created_at;