Q:

Sort by max date per ID, then other records by same ID, then other IDs by their max date

I have data with multiple rows per ID. I would like to first sort by maximum date, but keep the other rows with the same ID together by date descending, then the next group of IDs with the next max date, and so on.
For example, this data
create table #tbl (id int, dt date);
insert into #tbl (id, dt)
values (1, '2020-07-01')
     , (1, '2020-07-17')
     , (1, '2020-07-31')
     , (2, '2020-07-07')
     , (2, '2020-07-14')
     , (2, '2020-07-16')
     , (3, '2020-07-02')
     , (3, '2020-07-20')
;

would output as
id  dt
1   7/31/2020
1   7/17/2020
1   7/1/2020
3   7/20/2020
3   7/2/2020
2   7/16/2020
2   7/14/2020
2   7/7/2020

So id 1 has the greatest date, then the other id 1 rows by date descending. Next, id 3 has the greatest date of the remaining rows, then the other id 3 rows by date descending, and so on.
I can get max dates and row numbers but it is sorted by dates then IDs and doesn't keep the IDs grouped together.
Version: Microsoft SQL Azure (RTM) - 12.0.2000.8   Jul 31 2020 08:26:29   Copyright (C) 2019 Microsoft Corporation

A:

Use MAX() window function in the ORDER BY clause:
select * 
from #tbl
order by max(dt) over (partition by id) desc, 
         id, -- just in case 2 ids have the same max dt
         dt desc

See the demo.
Results:
> id | dt        
> -: | :---------
>  1 | 2020-07-31
>  1 | 2020-07-17
>  1 | 2020-07-01
>  3 | 2020-07-20
>  3 | 2020-07-02
>  2 | 2020-07-16
>  2 | 2020-07-14
>  2 | 2020-07-07

