HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to order by specific column without breaking groups?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
withoutordergroupshowcolumnbreakingspecific

Problem

I have a table like below and I want to order this table by date without breaking groups.

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-01


I 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-20

Solution

Another option, using a window function:

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.