Sorting with a secondary key means ordering records by one field and then, where two records tie, by a second field. Tasks usually say “sort by club, then by attendance with the highest first”.
This lesson sits in database queries and reports and follows applying multiple criteria.
How does a two-key sort work?
The software sorts by the primary key first. It then reorders only the records that share the same primary value, using the secondary key.
- Underline each sort instruction in the task and note ascending or descending for each.
- Put the keys in the order the task lists them: first mentioned is primary.
- Set the direction for each key separately.
- Check the result by reading down the primary column, then the secondary column inside each group.
Worked example
Using the invented Harbour Hill Clubs table from the module page.
Task: sort by Club in A to Z order, then by Sessions with the highest first.
Step 1, keys: primary Club ascending, secondary Sessions descending.
Step 2, groups: Chess, Drama, Robotics in alphabetical order.
Step 3, inside Chess: Imran (12), Dewi (11), Hui (10), Aiman (8).
Step 4, inside Drama: Bella (12), Chen (9), Gopal (6).
Step 5, inside Robotics: Farah (12), Evan (7), Jia (5).
The full order is therefore Imran, Dewi, Hui, Aiman, Bella, Chen, Gopal, Farah, Evan, Jia.
The mistake to watch for
Putting the keys the wrong way round, so Sessions becomes the primary key:
Sessions descending, then Club ascending.
The list starts Bella (12), Farah (12), Imran (12), then Dewi (11) and so on, mixing clubs together.
The records are all there, but the club groups have been scrambled. The task wanted groups first.
The correction is to place Club first and Sessions second. A quick test is to read the Club column: it should change only three times.
Check yourself
1. Sort by Year ascending, then Name A to Z. Write the order.
Show answer
Year 9: Dewi, Gopal, Jia. Year 10: Aiman, Chen, Farah, Imran. Year 11: Bella, Evan, Hui. Dewi, Gopal, Jia, Aiman, Chen, Farah, Imran, Bella, Evan, Hui.
2. Sort by Fee descending, then Name A to Z. Which three records come first?
Show answer
Fee 60 members are Evan, Farah and Jia, in A to Z order. Evan, Farah, Jia. Next would be the RM45 group: Bella, Chen, Gopal.
3. A student stores ID as text and sorts ascending. In what order do IDs 1 to 10 appear?
Show answer
Text sorts character by character, so “10” comes straight after “1”. The order is 1, 10, 2, 3, 4, 5, 6, 7, 8, 9. Fix it by storing ID as a number.
Where this leads next
Next, create a calculated field where supported so a report can show a value worked out from existing fields. The read-only SQL practice lab lets you see the order a query returns, and the practice set mixes all the skills.
Order mistakes are easy to repeat without noticing, which is where online one-to-one ICT tuition can help, because a teacher can watch your settings as you choose them.