3

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
3

I added also another group wit the same dates, so that you see that you have also to define an order for the group to be displayed, in case the same dates.

To get your wanted order i added in the CTE a sorting row, which is he max date for every Group

CREATE TABLE table1
    ("id" int, "group" varchar(6), "date" date)
;
INSERT INTO table1
    ("id", "group", "date")
VALUES
    (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'),
        (6, 'group4', '2011-05-05'),
    (7, 'group4', '2012-01-01')
;
WITH CTE AS (SELECT
"id", "group", "date"
,MAX("date") OVER (PARTITION BY "group") As maxdategroup
FROM
table1)
SELECT "id", "group", "date" FROM cTE ORDER BY maxdategroup DESC,"group" ,  "date" DESC
id | group  | date      
-: | :----- | :---------
 5 | group3 | 2012-01-01
 4 | group3 | 2011-05-05
 7 | group4 | 2012-01-01
 6 | group4 | 2011-05-05
 1 | group1 | 2011-11-24
 3 | group1 | 2011-03-11
 2 | group2 | 2011-08-20

db<>fiddle here

7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.