DISTINCT, Alias and ORDER BY

Build a Clean, Predictable Report

DISTINCT controls duplicate result rows, aliases improve labels and ORDER BY controls presentation order. Together they make a query easier to consume, but each solves a different problem.

SELECT
  s.full_name AS student_name,
  s.marks AS percentage
FROM students AS s
WHERE s.status = 'Active'
ORDER BY percentage DESC, s.student_id ASC;

Column and Table Aliases

AS names an output column or gives a table a short local name. It does not change storage.

SELECT s.full_name AS student_name,
       ROUND(s.marks / 100 * 5, 2) AS score_out_of_5
FROM students AS s;

Use clear aliases in reports and short table aliases in joins. A select-list alias can be used in ORDER BY, but generally not in WHERE because filtering is resolved before the select list.

ORDER BY with ASC and DESC

SELECT full_name, class_name, marks
FROM students
ORDER BY class_name ASC,
         marks DESC,
         student_id ASC;

Deterministic Ties and NULL

If two students share marks, ORDER BY marks DESC does not define which tied row comes first. Add student_id ASC. In ascending MySQL order, NULL sorts before non-NULL; in descending order it sorts after. To place recorded marks before missing marks explicitly:

ORDER BY marks IS NULL ASC,
         marks DESC,
         student_id ASC;

The boolean expression is 0 for recorded values and 1 for NULL, so recorded marks appear first.

DISTINCT with ORDER BY

SELECT DISTINCT class_name
FROM students
ORDER BY class_name ASC;

Keep ordering expressions compatible with the DISTINCT result. When a query becomes complex, select the needed result columns in a derived table or CTE and order the outer result clearly.

Mistakes and Practice

• Believing aliases change the schema.
• Relying on default or observed row order.
• Using column positions such as ORDER BY 2 in maintainable code.
• Forgetting a tie-breaker in paginated results.
• Adding DISTINCT to hide a bad join.

1. Sort active students by marks high-to-low and ID low-to-high.
2. Create a readable alias for a calculated five-point score.
3. List classes uniquely and alphabetically.
4. Place NULL marks last without relying on defaults.

Quick Summary

• Aliases label query output and shorten table references.
• ORDER BY defines result order; ASC is default and DESC reverses it.
• A unique tie-breaker makes output deterministic.
• DISTINCT and ORDER BY solve uniqueness and ordering separately.

Official References

• SELECT and ORDER BY — MySQL 8.4
• Problems with Column Aliases
• ORDER BY Optimization

References reviewed 14 August 2026.