How to order information using the DESC SQL query?-CodePudding

I was hoping someone could point me in the right direction as to why my data is not ordered by the flight_count column?

SELECT pilot_id, 
       pilot_firstname, 
       pilot_lastname, 
       pilot_email, 
       licence_num, 
       flight_count 
FROM pilots 
INNER JOIN 
           ( SELECT pilot_id, 
                   COUNT(flight_id) AS 'flight_count' 
             FROM flights
             GROUP BY pilot_id
             ORDER BY flight_count DESC
           ) as a
USING (pilot_id);

CodePudding user response：

Move the ordering criteria to the outer select:

SELECT p.pilot_id, p.pilot_firstname, p.pilot_lastname, p.pilot_email, p.licence_num, 
  fc.flight_count 
FROM pilots p
JOIN (
  SELECT pilot_id, COUNT(flight_id) AS flight_count 
  FROM flights
  GROUP BY pilot_id
) as fc
on fc.pilot_id = p.pilot_id
ORDER BY fc.flight_count DESC;

Note you should not be 'quoting' column aliases to delimit them, and the name is fine as-is; it's also generally a good idea to use (meaningful) table aliases explicitely, it helps with readability and also means there's less work for the query optimizer to do if columns are explicitely aliased.