clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249077 fiddles created (32128 in the last week).

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') ;
7 rows affected
 hidden batch(es)


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
 hidden batch(es)


select id, "group", date from table1 order by max(date) over (partition by "group") 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
 hidden batch(es)