Postgresql - last record in each group-CodePudding

Id   Name       Game
-------------------------
1    Xbox       Xbox_Game_1
2    Xbox       Xbox_Game_2
3    Xbox       Xbox_Game_3
4    Sony       Sony_Game_1
5    Sony       Sony_Game_2
6    PSP        PSP_Game_1

What query will return the following result?

3    Xbox       Xbox_Game_3
5    Sony       Sony_Game_2
6    PSP        PSP_Game_1

That is, the last record in each group should be returned.

CodePudding user response：

select distinct on (Name) Name, Game 
from Games 
order by Name, Id desc;

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal.

The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY

CodePudding user response：

You can use CTE (result here)

with ordered_games as (select row_number() over (partition by name order by id desc) as rn, id, Name, Game from Games)
select * from ordered_games where rn = 1