ORDER BY — SQL Technical Interview Questions & Answers

ORDER BY

ORDER BY

  • ORDER BY marks DESC; sorts the result. ASC is default (low to high). ORDER BY city, name; sorts by city, then name inside the same city.
  • ORDER BY 2 means second selected column — don’t do that in interviews; name the column. NULLs sort first or last depending on engine/settings — don’t assume.
  • LIMIT 5 after ORDER BY = top 5. Without ORDER BY, LIMIT is ‘some 5 rows’, not ‘best 5’.
  • Trap: ORDER BY RAND() on a huge table. Trap: sorting in PHP after SELECT *.
  • On the example next to this theory: ORDER BY — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Technical Interview Tip: ORDER BY marks DESC LIMIT 5; — top 5 marks.

Code Examples for ORDER BY

ORDER BY — sample query

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 100), (2, 'N', 80), (3, 'S', 120);
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING total >= 150
ORDER BY total DESC;

ORDER BY — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

How to Explain ORDER BY to an Interviewer

How do you get the highest marks first?

ORDER BY marks DESC