I have data like:

How to get only the latest index of each user ?

Like this result

CodePudding user response：

In PostgreSQL you can use the following:

WITH CTE(id,userr,indexx) AS 
 (
    select 1 ,  'aaa' , 0 union all
    select 2,   'bbb' , 0 union all
    select 3 ,  'aaa' , 1 union all
    select 4 ,  'bbb' , 1 union all
    select 5 ,  'aaa' , 2 union all
    select 6 ,  'ccc' , 0  
 )
 select distinct on(c.userr) c.userr,c.indexx,c.id
   from cte as c
     order by c.userr,c.id desc,c.id;

CodePudding user response：

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.