Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Reporting with SQL Ordering, Limiting and Paging Results Retrieving Results in a Particular Order

Posted by A X
A X
12,842 Points

Sorting Priority

Hi all, I'm watching the Order SQL series videos, and in Andrew's first video he goes over the ASC and DESC keywords. I was curious how SQL determines the ordering though with oppositional statements like in a movies database if we tell it to sort Marvel movies in ascending order by title but then producer by descending. Which gets executed first? Does it just depend on which is typed first in the SQL code?

2 Answers

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

The rules for sorting is that the order specified determines what gets priority. A second term is applied only when two or more items rank identically using the first term. This behavior is by design, not by chance.

Tommy Gebru
Tommy Gebru
30,164 Points
Tommy Gebru
Tommy Gebru
30,164 Points

Yeah at first i was a bit confused as well, but it makes sense as you mentioned when there are identical entries/values in the first column, the second column can recategorize the records ORDER BY ASC or DESC.

Maximillian Fox
PLUS
Maximillian Fox
Courses Plus Student 9,236 Points
Maximillian Fox
Maximillian Fox
Courses Plus Student 9,236 Points

Correct - it will conduct the ordering in the order you specify.

SELECT * FROM movies ORDER BY title ASC, producer DESC;
SELECT * FROM movies ORDER BY producer DESC, title ASC;

These queries will give you a different order.

  • The first query will order all the titles in alphabetical order, then the producers per title in descending order.
  • The second query will order all the producers in descending order, then the titles for each producer in ascending order which in this case may be more appropriate (since one producer is more likely to have worked on more than 1 title).

Have a go in the SQL playground and practice your ordering on a few different tables in there and see how it differs.