Databases & SQL · Class 12 · Querying with SELECT

MySQLSELECT⏱️ 8 min read

Sorting with ORDER BY

Rows come back in whatever order suits the server. That is fine for a dozen students and useless for a merit list. order by arranges the resultset — and, like an alias, it changes only what you are shown, never the table.

1Ascending order

MySQL command line client

mysql> select id, name, grade from students order by name asc;

+------+--------+-------+

| id | name | grade |

+------+--------+-------+

| 1187 | Anjali | 12 |

| 2500 | Bhim | 12 |

| 2199 | Diana | 11 |

| 3301 | Farhan | 9 |

| 2044 | Karma | 11 |

| 1402 | Lhamu | 10 |

| 3612 | Nima | 9 |

| 3120 | Pema | 10 |

| 2035 | Ravi | 12 |

| 1250 | Sohan | 12 |

| 3033 | Tom | 8 |

| 1099 | Veena | 10 |

+------+--------+-------+

12 rows in set (0.00 sec)

Alphabetical. asc is the default — leave it out entirely and you get the same result. It is worth writing anyway, because it says out loud which way you meant.

2Descending order

The merit list — highest first:

MySQL command line client

mysql> select id, name, percent from students order by percent desc;

+------+--------+---------+

| id | name | percent |

+------+--------+---------+

| 1402 | Lhamu | 95.2 |

| 1187 | Anjali | 91 |

| 1099 | Veena | 90.5 |

| 2199 | Diana | 88.25 |

| 2035 | Ravi | 83.75 |

| 2044 | Karma | 78.5 |

| 3301 | Farhan | 72.8 |

| 1250 | Sohan | 66 |

| 3033 | Tom | 64.25 |

| 3612 | Nima | 59.4 |

| 2500 | Bhim | NULL |

| 3120 | Pema | NULL |

+------+--------+---------+

12 rows in set (0.00 sec)

desc is not describe

In order by percent desc, desc means descending. In desc students; it is short for describe and shows the table's structure. Same three letters, completely unrelated jobs. The one place they can be confused is an exam question that mentions both.

3Where the NULLs go

Notice Bhim and Pema at the bottom of that list. Sort the same column ascending and they move to the top:

MySQL command line client

mysql> select id, name, percent from students order by percent asc;

+------+--------+---------+

| id | name | percent |

+------+--------+---------+

| 2500 | Bhim | NULL |

| 3120 | Pema | NULL |

| 3612 | Nima | 59.4 |

| 3033 | Tom | 64.25 |

| 1250 | Sohan | 66 |

| 3301 | Farhan | 72.8 |

| 2044 | Karma | 78.5 |

| 2035 | Ravi | 83.75 |

| 2199 | Diana | 88.25 |

| 1099 | Veena | 90.5 |

| 1187 | Anjali | 91 |

| 1402 | Lhamu | 95.2 |

+------+--------+---------+

12 rows in set (0.00 sec)

MySQL sorts NULL as lower than every real value: first when ascending, last when descending. This is the one context where NULLs are not simply dropped — they are still here, all twelve rows, just parked at one end.

Worth knowing for a merit list

order by percent desc puts the students with no result at the bottom, which is usually what you want. Sorting ascending would open the list with two blank rows.

4Sorting by two columns

Give several columns, separated by commas. The second is used only to break ties in the first — here, group by class, and within each class put the highest scorer first:

grades ascending, and inside each grade, marks descending

mysql> select id, name, grade, percent from students order by grade asc, percent desc;

+------+--------+-------+---------+

+------+--------+-------+---------+

| 3033 | Tom | 8 | 64.25 |

| 3301 | Farhan | 9 | 72.8 |

| 3612 | Nima | 9 | 59.4 |

| 1402 | Lhamu | 10 | 95.2 |

| 1099 | Veena | 10 | 90.5 |

| 3120 | Pema | 10 | NULL |

| 2199 | Diana | 11 | 88.25 |

| 2044 | Karma | 11 | 78.5 |

| 1187 | Anjali | 12 | 91 |

| 2035 | Ravi | 12 | 83.75 |

| 1250 | Sohan | 12 | 66 |

| 2500 | Bhim | 12 | NULL |

+------+--------+-------+---------+

12 rows in set (0.00 sec)

Each column gets its own direction — grade asc and percent desc in the same query. Read it as: sort by grade; wherever two students share a grade, put the higher mark first.

5Where the clause goes

select <columns>
  from <table>
 where <condition>
 order by <column> asc|desc;

order by is always last. Sorting happens after the rows have been chosen, which is the order the query is written in too. Putting it before the where is a syntax error.