Sorting Query Results Using the ORDER BY Clause

After learning how to retrieve data with SELECT and filter it with WHERE, the next step is organizing the results. In most cases, you don’t just want the right rows—you also want them displayed in a logical order. That’s where the ORDER BY clause comes in.

The ORDER BY clause sorts the rows returned by your query, either in ascending order (default) or descending order.

Basic ORDER BY syntax:

SELECT column1,
	   column2
FROM schema_name.table_name
ORDER BY column1 [ASC|DESC];

SELECT column1,
	   column2
FROM schema_name.table_name
ORDER BY column1 [ASC|DESC];

• ASC = ascending (smallest to largest, A → Z, earliest → latest)
• DESC = descending (largest to smallest, Z → A, latest → earliest)

Sorting in Ascending Order

Example: Sort paintings by year created in ascending order.

Table: Gallery.Paintings

Ascending is the default sort order, which means this query without ASC:

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
ORDER BY YearCreated;

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
ORDER BY YearCreated;

Will return the same result as this query with ASC:

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
ORDER BY YearCreated ASC;

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
ORDER BY YearCreated ASC;

Result:

Sorting in Descending Order

If you want the reverse order, add DESC:

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
ORDER BY YearCreated DESC;

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
ORDER BY YearCreated DESC;

Result:

Using WHERE and ORDER BY Together

It’s very common to filter rows with WHERE and then sort them with ORDER BY. The important rule to remember is:

Example: Find paintings created in the Netherlands and sort them by year:

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
WHERE CountryOfOrigin = 'Netherlands'
ORDER BY YearCreated ASC;

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
WHERE CountryOfOrigin = 'Netherlands'
ORDER BY YearCreated ASC;

Result:

📌 Important Note: ORDER BY must always come after WHERE. The database engine applies filtering before sorting, so your results are smaller and easier to organize.

Sorting by Multiple Columns

You can sort by more than one column. SQL will sort by the first column listed, and if there are ties, it will then sort by the next column.

Again, ASC is optional because it is the default sort order. When using both ASC and DESC in a single ORDER BY clause, I recommend explicitly stating ASC to make it clear that ascending is the intended sort order and not a forgotten DESC.

Example: Sort paintings first by country of origin (ascending), then by year created (descending):

SELECT Title,
	   Artist,
	   CountryOfOrigin,
	   YearCreated
FROM Gallery.Paintings
ORDER BY CountryOfOrigin ASC,
		 YearCreated DESC;

SELECT Title,
	   Artist,
	   CountryOfOrigin,
	   YearCreated
FROM Gallery.Paintings
ORDER BY CountryOfOrigin ASC,
		 YearCreated DESC;

Result:

Notice how:

• Countries are grouped alphabetically (Japan, Italy, Netherlands, etc.).
• Within each country, the paintings are ordered by year, newest to oldest.

Sorting by Column Position

Instead of writing the column name, you can also sort by the position of the column in the SELECT list.

Example: Sort paintings by the second column in the SELECT list (Artist):

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
ORDER BY 2 ASC;

SELECT Title,
	   Artist,
	   YearCreated
FROM Gallery.Paintings
ORDER BY 2 ASC;

Result:

📌 Important Note: While this works, it is considered poor practice. Using column names is generally better, and won’t require changes if the selected columns or column order changes in the future. Specifying column names also makes your queries easier to read and maintain.

Sorting with Expressions

Sorting isn’t limited to the column values as they are stored in the table. You can also sort using calculated values.

For example, suppose you want to see paintings grouped by the century they were created in. You can calculate the century by dividing the year by 100, then multiplying by 100.

SELECT Title,
	   Artist,
	   YearCreated,
	   (YearCreated / 100) * 100 AS Century
FROM Gallery.Paintings
ORDER BY (YearCreated / 100) * 100 ASC,
		 YearCreated ASC;

SELECT Title,
	   Artist,
	   YearCreated,
	   (YearCreated / 100) * 100 AS Century
FROM Gallery.Paintings
ORDER BY (YearCreated / 100) * 100 ASC,
		 YearCreated ASC;

Result:

💡Tip: This same expression could also be used in a GROUP BY clause—for example, to count how many paintings were created per century.

Key Takeaways

• Use ORDER BY to control the order of your query results.
• Sorting is ascending by default; add DESC for descending order.
• You can sort by multiple columns, with each column acting as a tiebreaker.
• Column positions can be used in ORDER BY, but column names are clearer and more reliable.
• Expressions and functions can also be used for custom sorting.

Sorting makes your query results easier to analyze and present, and it’s often one of the final steps before sharing your output.