SQL Statement does not order name in ascending order

Question

I am doing an SQL exercise, where the task is: "Arrange the result in descending order by the average paid loan fee and ascending by the first name and surname." Somehow I cannot manage to sort the name in ascending order. Does someone know how to solve it?

SELCT first_name, last_name, ROUND(avg, 2) AS Leihgebuehr
FROM ( 
   SELECT first_name, last_name, avg 
   FROM ( 
      SELECT customer.first_name, customer.last_name, 
      AVG(payment.amount) AS avg 
      FROM payment 
      INNER JOIN customer ON payment.customer_id = 
      customer.customer_id 
      GROUP BY first_name, last_name 
  ) 
WHERE avg > 5 
ORDER BY avg DESC, first_name ASC, last_name ASC
)

Answer 1

You query is mostly correct. The issue is that you are sorting first on the un-rounded AVG which likely is almost unique, so while the results do not look sorted correctly, the secondary sort order never comes into play.

To Prove this, add the un-rounded avg into your existing output so you can see the raw values.

Instead change the ORDER BY clause to sort the rounded value, not the raw value:

SELECT first_name, last_name, ROUND(avg, 2) AS Leihgebuehr
FROM ( 
   SELECT first_name, last_name, avg 
   FROM ( 
      SELECT customer.first_name, customer.last_name, 
      AVG(payment.amount) AS avg 
      FROM payment 
      INNER JOIN customer ON payment.customer_id = 
      customer.customer_id 
      GROUP BY first_name, last_name 
  ) 
WHERE avg > 5 
ORDER BY ROUND(avg, 2) DESC, first_name ASC, last_name ASC
)

Perhaps it is simpler to round the aggregate value when it is calculated, in fact we can use the HAVING clause to apply a filter condition on the result of the aggregate without having to use a sub query for a resultset this simple.

  SELECT 
    customer.first_name, 
    customer.last_name, 
    ROUND(AVG(payment.amount), 2) AS Leihgebuehr
  FROM payment 
  INNER JOIN customer ON payment.customer_id = customer.customer_id 
  GROUP BY first_name, last_name 
  HAVING AVG(payment.amount) > 5 
  ORDER BY ROUND(AVG(payment.amount), 2) DESC, first_name ASC, last_name ASC

We can actually futher simplify this, in most RDBMS it is valid to use the alias of column expressions in the ORDER BY clause, in this way we don't accidentally get the formula expression wrong, or as the expression evolves we might forget to update the expression both in the column definition or in the ORDER BY

NOTE: This alias usage ONLY works in ORDER BY not in WHERE, JOIN or other clauses.

SELECT 
  customer.first_name, 
  customer.last_name, 
  ROUND(AVG(payment.amount), 2) AS Leihgebuehr
FROM payment 
INNER JOIN customer ON payment.customer_id = customer.customer_id 
GROUP BY first_name, last_name 
HAVING AVG(payment.amount) > 5 
ORDER BY Leihgebuehr DESC, first_name ASC, last_name ASC