Purpose Of ORDER BY

ORDER BY changes the order in which query results are displayed. It does not change the stored records or field values in the table.

The sort field can be selected for display, but it does not have to be. The database can arrange records using a field that is not included in the result columns.

Cambridge limits the required forms to ORDER BY ASCENDING and ORDER BY DESCENDING. Follow the syntax and wording provided by the examination question.

Ascending Order

Ascending numeric order runs from the smallest value to the largest. Ascending text order runs alphabetically from A to Z. Ascending date/time order runs from the earliest to the latest.

A query that orders marks ascending displays the lowest mark first. A query that orders names ascending displays names beginning with earlier letters first.

If two records have equal sort values and no second sort field is specified, do not invent a tie-breaking rule. Preserve the shown order unless the question provides more information.

Descending Order

Descending order reverses the direction: largest to smallest, Z to A, or latest to earliest.

A query used to find the highest prices near the top of a result would order UnitPrice descending. A list of most recent appointments first would order AppointmentDate descending.

Read words such as highest first, latest first and reverse alphabetical as indicators of descending order.

Syllabus-Style Queries

The first query returns all products and sorts them from highest to lowest price. The second first filters records to YearGroup 10 and then sorts the matching names alphabetically.

ORDER BY follows FROM and any WHERE condition in the query.

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESCENDING;

SELECT StudentName
FROM Students
WHERE YearGroup = 10
ORDER BY StudentName ASCENDING;

Tracing Sorted Results

Expected Orders

Filtering Before Sorting

Conceptually, WHERE determines which records are included and ORDER BY arranges those matching records. A record that fails the WHERE condition cannot appear simply because of its sort value.

When tracing a query, first mark the matching records, then sort only those records, then display the selected fields. This fixed method reduces mistakes.

Ascending And Descending In Answers

State the direction clearly. Writing ORDER BY Price without the required direction may be incomplete when the question asks for a particular order.

Do not replace the syllabus wording with unsupported query features. Use the form shown in the paper and notes.

Worked Examples

Examination Guidance

• Translate earliest, smallest and A to Z as ascending.
• Translate latest, largest and Z to A as descending.
• Apply WHERE before sorting when tracing output.
• Remember sorting does not alter stored data.
• Use the exact syntax style supplied by the paper.

Knowledge Check

1. What does ORDER BY change?

2. What is ascending numeric order?

3. What is descending date order?

4. Does sorting change stored data?

5. When tracing, what is done before sorting?