Structured Query Language · Cambridge syllabus 9.4 · Lesson 3 of 4
Sorting and Selection
ORDER BY sorts the result, using ASC for ascending or DESC for descending order. SUM adds up the values in a numeric field and COUNT counts the records, for the records that match the WHERE clause. Selecting fields, filtering records and ordering output are separate operations.
By the end, you can
- Sort query results in ascending or descending order with ORDER BY.
- Select specific fields.
- Interpret the output of SQL queries.
- Use SUM and COUNT in queries.
Query results
ORDER BY sorts the records a query returns. ORDER BY Surname ASC puts them in ascending order — smallest first, earliest first, A to Z — and ascending is also what happens if neither is written; ORDER BY Price DESC puts them in descending order, largest first.
An exam query sorts on one field, named straight after ORDER BY, with ASC or DESC after it when the order matters. Choosing the field is part of reading the question: cheapest first means ORDER BY Price ASC, and newest first on a date field means ORDER BY DateJoined DESC.
It sorts by the stored data type, which is where a poor design shows itself. Dates stored as dates sort chronologically; dates stored as text sort alphabetically and put every December before every January. Numbers stored as text sort 1, 10, 2.
Selecting specific fields is the other half of shaping a result. Listing only the fields needed makes the output readable and the query faster, and the field order in the SELECT clause is the column order in the result.
Reading the output of a query means checking three things: are these the right records, are these the right fields, and are they in the intended order. A query can return exactly the right records and still be wrong for the question if the sort was not asked for.
SUM and COUNT return a single value instead of a list of records. SELECT SUM(Price) FROM Books WHERE Category = "Science" adds up the prices of the science books. SELECT COUNT(*) FROM Books WHERE OnLoan = TRUE counts how many books are on loan, and COUNT(Title) counts the records that have a title. SUM only works on a numeric field; COUNT works on any.
Worth remembering
- ORDER BY ASC sorts ascending, the default; ORDER BY DESC sorts descending.
- Sort on the field the question names, in the direction it asks for.
- Fields are returned in the order the SELECT clause lists them.
- SUM adds a numeric field; COUNT counts the records that match.
Worked example
SELECT Name, Score FROM Students ORDER BY Score DESC lists the highest scores first.
Practice
Warm-up · not marked
Check your understanding
Answer in your own words, then compare with the worked answer. Nothing here is saved or scored.
Worked answer
SELECT Name, Score FROM Students WHERE Score >= 50 ORDER BY Score DESC
Check your method, units and explanation against it.
Marked practice
Set 1 of 5 · easiest first · Checked as you go, with the worked answer. Sign in or create a free account to save your progress.