What Does ORDER BY 1 Mean in SQL?

Have you ever seen the "ORDER BY 1" syntax in SQL queries and wondered what it means? In this article, we'll explore this syntax, clarify its purpose, and make it easier to grasp.

Whether you're an experienced SQL user or just starting to explore this language, you've likely encountered the ORDER BY clause before. In SQL, the ORDER BY clause is used to sort the rows in a table based on one or more columns. It allows you to arrange your data in ascending (A-Z, 1-10) or descending (Z-A, 10-1) order according to specific criteria.

After the ORDER BY clause, you typically specify the column by which you want to sort the data. However, you may be surprised to learn that you can also utilize syntax like ORDER BY 1 in a SQL query. The syntax for such a query is as follows:

SELECT column_name(s)
FROM table_name
ORDER BY 1;

If you've seen this kind of code in SQL queries and it left you puzzled, don't worry! By the end of this article, you'll understand exactly how it works and when to use it. And if you ever need to brush up on SQL syntax while reading the article, don't hesitate to check out our handy SQL Basics Cheat Sheet!

To get really good at SQL, you should practice it. If you want lots of practice problems to work on, we recommend trying our SQL Practice path. You'll learn by solving real-world problems, using our online code editor and real datasets. This path consists of nine courses and over 1,100 hands-on exercises to practice with.

Let's See ORDER BY 1 in Action!

Imagine that you have a table called movie with the following columns:

• id – The ID of the movie, which is also the primary key (PK) of the table.
• title – The title of the movie.
• length – The length of the movie in minutes.
• director – The name of the movie’s director.
• year – The year when the movie was first released.
• rating – The rating of the movie (from 1 to 10).

Here you can see a handful of rows from the table:

What happens if we run the following query?

SELECT 
	title,
	length,
	director
FROM movie
ORDER BY 1;

By specifying ORDER BY 1, we're instructing the database to sort the results based on the first column in the SELECT list, which is title in this case.

This means that the above query is equivalent to this one:

SELECT 
	title,
	length,
	director
FROM movie
ORDER BY title;

The first query is a little shorter, but it can be harder to understand. In the second query, you can easily see which column is used for sorting. This reduces the chance of making an error.

This is the result of our query:

If the order of columns selected changed, you would have to use a different column number:

SELECT
	director,
	title,
	length
FROM movie
ORDER BY 2;

As you can see, using this syntax can be a little tricky.

Did you know that you can use a similar syntax in the GROUP BY clause? Check out our article on GROUP BY 1 syntax!

Ascending and Descending Order

By default, the ORDER BY clause sorts the results from lowest to highest – i.e. in ascending order. However, sometimes you might need to sort the data in descending order. You can do it using the ORDER BY 1 syntax as well. All you have to do is add the ASC or DESC keyword after the 1.

For example, if you wanted to retrieve the list of the movies and sort it from the longest to the shortest, you could run a query like this:

SELECT
	title,
	length,
	director
FROM movie
ORDER BY 2 DESC;

The above query is the equivalent of:

SELECT
	title,
	length,
	director
FROM movie
ORDER BY length DESC;

And here is the result of our query:

What Does ORDER BY 1, 2, 3 Mean?

In our previous example, we sorted the data based on just one column. Now, we would like to order our rows based on three different columns: year, rating, and title.

To do that, we can use the following query:

SELECT
	year,
	rating,
	title
FROM movie
ORDER BY 1, 2, 3;

When you use ORDER BY with multiple columns, the database first sorts the rows based on the first specified column. If there are duplicate values in the first column, the database then sorts those rows further using the second specified column, and so on. When using ORDER BY 1, 2, 3, we're instructing the database to sort the results based on the first, second, and third columns in the SELECT list.

This is equivalent to running the following query:

SELECT
year,
rating,
title
FROM movie
ORDER BY year, rating, title;

Here is what the query returns:

The above query sorted the results in ascending order for all the columns. Well, what if we wanted to sort the results by year from newest to oldest, then by rating from highest to lowest and eventually by title alphabetically? We can do all of that by writing ASC or DESC after each column:

SELECT
	year,
	rating,
	title
FROM movie
ORDER BY 1 DESC, 2 DESC, 3 ASC;

The result of this query is:

Note that the column numbers in the ORDER BY clause do not have to be consecutive. You can also write a query like this:

SELECT
	title,
	year,
	rating
FROM movie
ORDER BY 2 DESC, 3 DESC, 1 ASC;

Now, we're instructing the database to group the results based on the second, third, and first column in the SELECT list.

You can also mix column numbers and names in a single ORDER BY clause:

SELECT
	title,
	year,
	rating
FROM movie
ORDER BY 2 DESC, 3 DESC, title ASC;

This time, we're grouping the results based on the second and third column  in the SELECT list and the title column.

The above queries return the following:

Caution: Do Not Use the ORDER BY 1 Syntax

Using the  ORDER BY 1 and ORDER BY 1, 2, 3 syntax in SQL is generally considered a bad practice because it makes your queries harder to read and maintain. While it can save you some typing, it can also cause unexpected issues.

The main problem with this approach is that it relies on the order of columns in your SELECT statement rather than specifying the column names directly. If the order of columns changes or new columns are added, your query results might change in ways you didn't expect.

For example, let's say that you used the following query to get the list of the movies sorted by year from newest to oldest, then by rating from highest to lowest and eventually by title alphabetically:

SELECT
	title,
	year,
	rating
FROM movie
ORDER BY 2 DESC, 3 DESC, 1 ASC;

Now, imagine that we'd also like to display the director's name in our list. We can achieve this by modifying the query a bit:

SELECT
	title,
	director,
	year,
	rating
FROM movie
ORDER BY 2 DESC, 3 DESC, 1 ASC;

But when we run this query, it doesn't give us the right answer! This is the result we get:

It looks like the rows are not sorted by  year, rating, and title as intended. This demonstrates the potential pitfalls of relying on column order instead of specifying column names directly in the ORDER BY clause. Can you spot the mistake in our modified query?

The result is incorrect because we tried to sort our rows by the director, year, and title columns instead of year, rating, and title.

The correct query would look like this:

SELECT
	title,
	director,
	year,
	rating
FROM movie
ORDER BY 3 DESC, 4 DESC, 1 ASC;

But we can prevent this error by simply using the ORDER BY clause with the complete column names:

SELECT
	title,
	director,
	year,
	rating
FROM movie
ORDER BY year DESC, rating DESC, title ASC;

Now, even if we decide to change or move columns in the SELECT statement, we don't have to think about the ORDER BY part.

As you can see, it's better to explicitly mention the names of the columns you want to sort by in the ORDER BY clause. This makes your query easier to read and less likely to have mistakes.

The ORDER BY 1 trick is okay when you're just looking at data by yourself and want to speed up typing. But if you plan to reuse the query in any way, it's better to use the full syntax with column names.

Curious to Find Out More About ORDER BY 1?

That's all we've got for today! If you want to know more about how to use the ORDER BY clause, take a look at our articles to learn how to use ORDER BY in detail and see some real-life examples of using ORDER BY.

And for more hands-on practice, remember to check out our SQL Practice track!