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 |
-
Please can you edit your question to include what research you've done so far - if you've tried some SQL and it didn't give the result you want, show that, along with the output or exact error message you got; if you've found other questions here or links online, include those and explain why they weren't enough for you to make progress.IMSoP– IMSoP2021-10-05 12:52:39 +00:00Commented Oct 5, 2021 at 12:52
Add a comment
|
2 Answers
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;
Comments
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.
2 Comments
jarlh
Looks like OP wants the result in id order.
Laurenz Albe
To sore the result in
id order, you can wrap the query from the answer with SELECT * FROM (...) AS q ORDER BY id;.