ORDER BY: Bringing Order to the World
ORDER BY: Bringing Order to the World
The home page of StackOverflow is a great place to see an ordered list of rows.
StackOverflow example of ORDER BY
The home page is a list of the most recent questions that have come into the site:
Down the right hand side, you’ll see things like “1 min ago” and “2 mins ago.” Let’s go get that data out of the database. In past exercises, we’ve gotten the contents of the dbo.Posts table, but let’s put them in reverse order of CreationDate.
I’m using a specific field list here because dbo.Posts has a lot of fields on it, and I want my screenshot to reflect the fields I like the most:
|
1 2 3 |
SELECT TOP 10 Id, CreationDate, Title, Body FROM dbo.Posts ORDER BY CreationDate DESC; |
The ORDER BY tells SQL Server to first go get the data, then sort it in the order that we’d like to show it onscreen – in this case, DESC means DESCending. Run that query, and here’s the results:
This gives me the most recent posts.
Now it’s time for a little confession: you’re probably not using the live StackOverflow.com database. Data.StackExchange.com is actually a restored copy of the production database, and it lags several days behind production. That means the stuff you see on StackOverflow.com won’t exactly match the database queries you’re running. That also means you can see the exact date/times when I wrote this training material.
If I turn that around and sort by CreationDate ASC, I’ll get the first 10 posts instead of the last 10:
|
1 2 3 |
SELECT TOP 10 Id, CreationDate, Title, Body FROM dbo.Posts ORDER BY CreationDate ASC; |
Run that query, and here’s the results:
You can also order by multiple columns. Now, I know what you’re thinking: based on our WHERE examples, you’d expect to say something like this:
|
1 2 3 |
SELECT TOP 10 * FROM dbo.Posts ORDER BY CreationDate AND Title; |
But that’s not how it works. Instead, we need to put them in a comma-delimited list:
|
1 2 3 |
SELECT TOP 10 * FROM dbo.Posts ORDER BY CreationDate, Title; |
In that example, I didn’t bother specifying ASC or DESC sort – if you don’t, then ASCending is the default.
Now, you try.
Go to the home page of StackOverflow.com, then click on the Month tab. Look closer at the posts listed on this page – what order are they sorted in? Write a query that returns the posts in that order.
When you’re done with that, move on to the next lesson.