snippetsqlMinor
How to order by specific column without breaking groups?
Viewed 0 times
withoutordergroupshowcolumnbreakingspecific
Problem
I have a table like below and I want to order this table by date without breaking groups.
I only consider the latest updated group element when ordering by date.
I want to get order like;
id group date
1 | group1 | 2011-11-24
2 | group2 | 2011-08-20
3 | group1 | 2011-03-11
4 | group3 | 2011-05-05
5 | group3 | 2012-01-01I only consider the latest updated group element when ordering by date.
I want to get order like;
id group date
5 | group3 | 2012-01-01
4 | group3 | 2011-05-05
1 | group1 | 2011-11-24
3 | group1 | 2011-03-11
2 | group2 | 2011-08-20Solution
Another option, using a window function:
Thnx to @nbk for providing the dbfiddle.uk
select
id, "group", date
from
table1
order by
max(date) over (partition by "group") desc,
"group",
date desc ;Thnx to @nbk for providing the dbfiddle.uk
Code Snippets
select
id, "group", date
from
table1
order by
max(date) over (partition by "group") desc,
"group",
date desc ;Context
StackExchange Database Administrators Q#285668, answer score: 6
Revisions (0)
No revisions yet.