Sorting Results with ORDER BY

Hello students, welcome to Kwickprep. A merit list shows the topper first. But a table has no fixed order, so how does SQL build that list? Today we will sort results with order by, sort on two columns, and combine where with order by.

All our examples use one table called student. It has five columns: roll, name, section, marks and city. Kabir was absent for the test, so his marks are NULL. NULL means no value at all. It is not zero, and it is not a blank space.

Order by sorts the rows in the result. Ascending means from small to big, and it is the default. So order by marks puts the lowest marks first. In MySQL, NULL counts as smaller than every value, so Kabir comes first in ascending order.

Descending means from big to small, and we write DESC after the column. Now Meera, the topper, comes first. This is our merit list. Kabir with NULL marks moves to the end in descending order in MySQL.

Let us compare the two keywords. ASC means ascending, from small to big. DESC means descending, from big to small. If you write nothing, SQL uses ascending.

Order by also works on text. Text is sorted in dictionary order, from A to Z. So Aman comes first and Riya comes last. With DESC, text goes from Z to A. Dates can be sorted too, from the oldest to the newest.

Sometimes the first column has repeated values. For example, three students are in section A. The second column decides the order inside each group, which is called breaking the tie. A teacher may want the list by section, and toppers first within each section.

Separate the columns with a comma. First, rows are sorted by section, so A comes before B. Inside section A, rows are sorted by marks in descending order. So Meera, Riya, then Neha. Section B follows the same rule.

Here is a trap that exams love. Each column gets its own direction. In sec, marks DESC, the DESC belongs only to marks. Section is still sorted ascending. If you want both descending, write DESC after each column.

Now we combine where and order by. Where first picks the rows from Surat, which are Riya and Neha. Then order by sorts only those rows, with high marks first. So Riya comes before Neha.

The clauses must be written in a fixed order. Select comes first with the columns. From comes next with the table. Where comes after from. Order by always comes last. Writing order by before where gives an error.

Pause and predict before you look at the output. Which students have marks above seventy? Riya, Aman and Meera. Kabir is dropped, because NULL is never greater than seventy. Now sort the names from Z to A. The answer is Riya, Meera, then Aman.

Some exams write table and field names with capital first letters, and ask you to complete such a query. The pattern is the same. Select the fields, from the table, where the condition, and order by the field last.

Let us revise what we learned today. Order by sorts the result, and ascending is the default. DESC sorts from big to small. With two columns, the second one breaks ties in the first. DESC applies only to the column written just before it. And the clause order is select, from, where, then order by. Make a merit list of your own class marks for practice.