SQL sort and order table-CodePudding

I've been trying this but haven't achieved the exact result I want.

I have this table:

id  aviacompany destination departure_time  duration
1   Alaska Air  ATL                09-45    180
2   Alaska Air  ATL                00-56    195
3   Alaska Air  LAS                01-00    304
4   JetBlue     MIA                06-10    256
5   JetBlue     ATL                09-50    200
6   JetBlue     MIA                18-43    270
7   JetBlue     SFO                23-24    180
8   Silver      DTW                07-10    120
9   Silver      SEA                18-01    137
10  Silver      DTW                19-32    183

I want to sort by the total number of flights operated by each aviacompany in descending order;

• In case of ties, sort by name of the aviacompany in alphabetical order

• Then, sort by the total number of flights to a particular destination operated by the same aviacompany

• In case of ties, sort by the destination column in alphabetical order

• Finally, if there are still ties, sort by the departure_time column in chronological order.

Output should look like this:

id  destination departure_time
4   MIA         06-10
6   MIA         18-43
5   ATL         09-50
7   SFO         23-24
2   ATL         00-56
1   ATL         09-45
3   LAS         01-00
8   DTW         07-10
10  DTW         19-32
9   SEA         18-01

Thanks

CodePudding user response：

You can use window functions to derive the required sort criteria, assuming you are using MySql 8

select id, destination, departure_time
from (
    select *, 
    Count(*) over(partition by aviacompany) aQty,
    Count(*) over(partition by aviacompany,destination) dQty
    from t
)t
order by 
  aQty desc, 
  aviacompany,
  dQty desc, 
  destination, 
  departure_time

Demo Fiddle