Can we apply ORDER BY with multiple columns?

Question

In SQL, can we apply ORDER BY with multiple columns?

Answer

Yes, following the ORDER BY, you can list more than one column for which to order the data by.

When ordering by more than one column, it will first order the data on the first column, then, keeping the previous column order, it will order on the next column, and so on.

You can also specify ascending or descending order for each listed column.

Example

/* 
This will order on the year, then order the 
names in reverse alphabetical 
order, preserving the order
of the year column.
*/

SELECT year, name
FROM movies
ORDER BY year ASC, name DESC;
18 Likes

In the example that you had posted, won’t the resulting data be incorrect? As in, the year won’t be matching the name of the movie?

4 Likes

Hello :slight_smile: Welcome to the forum!

No, this will not happen. A row is treated as a consistent entity. ORDER BY clause sorts whole selected rows by the given columns (year & name).


Take a look at this table named bugs:

id				course_id		exercise_id		reported_by
1				14				2				Alex
2				5				4				Tod
3				14				4				Tod
4				14				6				Tod
5				5				4				Alex
6				5				3				Roy
7				14				2				Roy
8				7				4				Alex
9				5				4				Roy
10				7				8				Tod

Now, let’s run this query: SELECT * FROM bugs ORDER BY reported_by;, this is the result:

id				course_id		exercise_id		reported_by
1				14				2				Alex
5				5				4				Alex
8				7				4				Alex
6				5				3				Roy
7				14				2				Roy
9				5				4				Roy
2				5				4				Tod
3				14				4				Tod
4				14				6				Tod
10				7				8				Tod

Please notice that in both results rows with reported_by = Alex have ids 1, 5 and 8 :slight_smile:

9 Likes

Thanks for the reply!

I get what you’re saying, but my question was for multiple columns selected for the ORDER BY clause.

So in the example originally posted, they had ordered by ‘year’ in the ascending order and then ‘name’ in the descending order.

So my question was which order will prevail in the end? Because the result set will be different for both the orderings individually.

9 Likes

Oh, sorry for misunderstanding your question.

Ok, so the same table as before - bugs. This is the result of SELECT * FROM bugs ORDER BY course_id ASC, exercise_id DESC:

id			course_id		exercise_id		reported_by
2			5				4				Tod
5			5				4				Alex
9			5				4				Roy
6			5				3				Roy
10			7				8				Tod
8			7				4				Alex
4			14				6				Tod
3			14				4				Tod
1			14				2				Alex
7			14				2				Roy

The data was first ordered by course_id ascending. And then each group of the rows (course_id = 5, course_id = 7, course_id = 14) was ordered by exercise_id in a descending manner.

65 Likes

Awesome, thank you so much! :slight_smile:

1 Like

You’re very welcome! :panda_face:

1 Like

Thank you for clearing that up, was also wondering the same thing.

Cheers,
Ben

7 Likes

Hi thank you for this discussion. I conclude that by writing order by in 2 condition is like making group in a group and so on.
I wonder since I see the same names in ‘reported by’ but with different id, is that refer to the same person? If yes, how we could differentiate if in actual condition the person are NOT the same even that they have same name?
Thank you.

is this print two different table
or what or give priority to first condition

1 Like

Hello,
When you say it’s ordered in order of the first column and then the next and so on, is that the order of the columns in the table or the order I ask them to be ordered?

So in this case it is only useful if you have columns that contain 2 or more rows that have the same data otherwise it will sort it by the first column name that comes after the ORDER BY command?

As said when using order by clause in multiple columns it 1st sort on the basis of 1st column then 2nd column and so on… its mean the sorting depend on very last column?

I tried this and this seems to be correct. The first condition takes priority, while the second maintains its order, unless the first interferes.

having multiple columns in order by clause will make complex sorting - you can simulate behavior in excel when using sorting feature with selecting multiple columns. Basically subsequent columns provide order inside groups of previously sorted data

1 Like

thanks for this as well! i was wondering the same. Thanks dplegaleagle for asking the question!

Nothing tells us that the name is a primary key. If we see the name appearing more than once, it’s not a unique identifier. We are talking about bugs, so id is the primary key. Every time a person reports a bug, that person gets a new bug id. You can report a bug for over-billing, and if you want to report an issue for poor internet connectivity, you’d have to create another ticket. So you’ll get another bug id. If you see on @factoradic second table, no two bug ids are listed. It means one record for one bug id. I say bug id because they table name is bugs. I understood that way.

Of course two people having the same name is a possibility. But every new bug reported would have a new and unique id.

It’s the order of the columns listed in the ORDER BY clause. You try interchanging the position of the columns listed every time you need to go see the first one would be the one the have the condition satisfied. This is not to be confused with order of appearance of columns in the SELECT Column1 , Column2 , Column3 , Column4 … statement which should be our desired order of appearance of columns in the result.

@dplegaleagle @alifaiz1804 @bitmaster17549

To clarify, When we order by multiple columns, the first column we order by sets the bases for the rest of the columns we want to order by. Imagine we want to order the movies by YEAR, and NAME.
All movies are first ordered by the YEAR. Now if Two movies "John Wick and “Annabelle” were released in the same year, say 2014, we can now do a second ordering which places the row for “John Wick” above that of “annabelle” when we order in descending order.
This can be seen in the picture below:

.

3 Likes