Postgresql OVER

I have data like:

id	user	index
1	aaa	0
2	bbb	0
3	aaa	1
4	bbb	1
5	aaa	2
6	ccc	0

How to get only the latest index of each user ?

Like this result

id	user	index
4	bbb	1
5	aaa	2
6	ccc	0

Answer

Looks like a simple DISTINCT ON:

SELECT DISTINCT ON ("user")
       id, "user", "index"
FROM mytab
ORDER BY "user", "index" DESC;

This will return the first row per "user" in the specified ordering, so the maximum "index".

It is a good idea to avoid column names that are reserved SQL keywords.