SQL get entries where on attribute is max

user_13268423 0
Viewed 29

I have the following dataset:

id id_rev time
1 1 08.01.2022
1 0 31.02.2021
2 2 28.01.2017
2 1 25.07.2021
2 0 25.07.2021

I am looking for a SQL query that can return an entry per id but only the one where the id_rev is maximum. So in this case it should return these two rows:

(id=1, id_rev=1,time) (id=2, id_rev=2, time)

max sql
2 Answers

One canonical approach uses ROW_NUMBER:

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

SELECT id, id_rev, time
FROM cte
WHERE rn = 1
ORDER BY id;

Another approach would be to use exists logic:

SELECT id, id_rev, time
FROM yourTable t1
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable t2
    WHERE t2.id = t1.id AND t2.id_rev > t1.id_rev
);
user_18632290

@result = SELECT *, RANK() OVER (PARTITION BY id ORDER BY id_rev DESC) AS Rank FROM dataset ORDER BY Rank;

@result = SELECT * FROM @result WHERE Rank = 1;

user_121222250
Related