My table looks like:
id | invoice | newlevel | status
----------------------------------------
1 | 123 | 1 |
2 | 123 | 2 | 2abc
3 | 555 | 1 |
4 | 123 | 3 | 3xyz
5 | 555 | 2 | 2abc
6 | 999 | 5 | 5YYY
How do I
SELECT invoice, MAX(newlevel) AS currentlevel FROM example GROUP BY invoice
When I execute the query, it returns
id | invoice | currentlevel | status
----------------------------------------------
4 | 123 | 3 |
5 | 555 | 2 |
6 | 999 | 5 | 5YYY
The column status seems to be the first result (from id = 1), the other data is correct.
I tried everything like union multiple querys, then group by or subquerys in 3 level.
UPDATE: @Barmar was right, this is an duplicate. My solution was the Answer from @Kevin Burton in the original post
SELECT *
FROM t1 WHERE (id,rev) IN
( SELECT id, MAX(rev)
FROM t1
GROUP BY id
)
