SQL ORDER BY Multiple Columns: Apply a Separate Direction to Each Key

The practice task

Sort a fictional directory by department ascending and name descending within each department. The same name appears in two departments, so sorting names globally would answer a different display request.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE directory(id INTEGER, dept TEXT, name TEXT);
INSERT INTO directory VALUES (1,'Design','Ari'),(2,'Support','Bo'),(3,'Design','Cy'),(4,'Support','Ari');

SELECT dept, name FROM directory
ORDER BY dept ASC, name DESC, id ASC;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Design appears before Support. Within Design, Cy appears before Ari because that key is descending. Within Support, Bo appears before Ari. The second key affects rows sharing the same department; it does not override the primary department grouping.

Writing ORDER BY dept, name DESC applies DESC only to name. State directions on both keys when readability matters. A reader should not need to remember a default in order to understand how the directory is arranged.

Text ordering depends on collation and database settings. This fixture uses short ASCII labels so the intended result is easy to inspect. For names in different scripts or case-sensitive product codes, test the required collation rather than assuming every deployment sorts text identically.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Assign direction to the intended individual key.
• Include a tie case in the fixture.
• Use a stable unique key when earlier columns can all tie.