I have the following dataset:

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)

CodePudding user response：

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
);

CodePudding user response：

@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;