Coding truck

Sorting results with the order by sql clause

In the past few posts we have learned about database tables, selecting data and the basic sql clauses. In this post we are going to learn about sorting those results. Previously, we learned that sql is built using clauses. These clauses perform different functions when describing a data set.

Thinking of sql as describing a data set is a good way to write it. The different clauses describe different aspects of the data. Here are the clauses thus far:

  • Select - The columns returned
  • From - Where the columns are from
  • Where - Which rows are returned
  • Order by - The order that the data is returned in

The order by clause is new, it will allow you to sort your results. Let's try it out

The expanded menu

First of all, lets create a table to try out the order by clause on. Here is the menu table. This time with a larger number of dishes.

SQL Fiddle

Create table Menu (
  Dish varchar(50)
  ,Price decimal(5,2)
);


Insert Into Menu(Dish, Price) Values ('Asada Fries', 0.99);
Insert Into Menu(Dish, Price) Values ('Barbacoa', 1.99);
Insert Into Menu(Dish, Price) Values ('Carnitas', 2.99);
Insert Into Menu(Dish, Price) Values ('Double Cheeseburger', 3.99);
Insert Into Menu(Dish, Price) Values ('Egg Rolls', 4.99);
Insert Into Menu(Dish, Price) Values ('Frites', 5.99);
Insert Into Menu(Dish, Price) Values ('Garlic Naan', 6.99);
Insert Into Menu(Dish, Price) Values ('Hamburger', 7.99);
Insert Into Menu(Dish, Price) Values ('Ice cream', 8.99);
Insert Into Menu(Dish, Price) Values ('Jalapeno Poppers', 9.99);
Insert Into Menu(Dish, Price) Values ('Kimchi', 9.99);
Insert Into Menu(Dish, Price) Values ('Lasagna', 0.99);
Insert Into Menu(Dish, Price) Values ('Milkshake', 1.99);
Insert Into Menu(Dish, Price) Values ('Noodles!', 2.99);
Insert Into Menu(Dish, Price) Values ('Octopus', 3.99);
Insert Into Menu(Dish, Price) Values ('Peach Cobbler', 4.99);
Insert Into Menu(Dish, Price) Values ('Quinoa Salad', 5.99);
Insert Into Menu(Dish, Price) Values ('Roast Beef Sandwich', 6.99);
Insert Into Menu(Dish, Price) Values ('Sausage on a Stick', 7.99);
Insert Into Menu(Dish, Price) Values ('Tacos', 8.99);
Insert Into Menu(Dish, Price) Values ('Udon', 9.99);
Insert Into Menu(Dish, Price) Values ('Vanilla Wafers', 0.99);
Insert Into Menu(Dish, Price) Values ('Water', 1.99);
Insert Into Menu(Dish, Price) Values ('Xiaolongbao', 2.99);
Insert Into Menu(Dish, Price) Values ('Yogurt (frozen)', 3.99);
Insert Into Menu(Dish, Price) Values ('Zirisk Pollo', 4.99);

Ordering the menu

First, lets select the menu table ordered by dish. Notice that the order by clause is the last clause in the sql statement.

Select * 
From Menu
Order by Dish

Our results show us all the dishes in the menu table ordered alphabetically. By default they are in ascending order. Sadly, this looks like the results we returned when we didn't have an order by clause. The difference is that when you use the order by clause, your results are guaranteed to be in the order you specify.

Dish Price
Asada Fries 0.99
Barbacoa 1.99
Carnitas 2.99
Double Cheeseburger 3.99
Egg Rolls 4.99
Frites 5.99
Garlic Naan 6.99
Hamburger 7.99
Ice cream 8.99
Jalapeno Poppers 9.99
Kimchi 9.99
Lasagna 0.99
Milkshake 1.99
Noodles! 2.99
Octopus 3.99
Peach Cobbler 4.99
Quinoa Salad 5.99
Roast Beef Sandwich 6.99
Sausage on a Stick 7.99
Tacos 8.99
Udon 9.99
Vanilla Wafers 0.99
Water 1.99
Xiaolongbao 2.99
Yogurt (frozen) 3.99
Zirisk Pollo 4.99

Still, let's mix it up a bit and try something else. This time we will bring back all the dishes on the menu with a price less than three dollars. Also we will order the results in descending order alphabetically by dish name.

Select * 
From Menu
Where Price < 3
Order by Dish desc

This time the results are in reverse order and the prices are all in the range that we expected. Notice that we changed two aspects of our data.First, we added a where clause which reduced the number of records returned. Second, we used the order by clause to sort in descending (desc) order rather than ascending (asc) order.

Dish Price
Xiaolongbao 2.99
Water 1.99
Vanilla Wafers 0.99
Noodles! 2.99
Milkshake 1.99
Lasagna 0.99
Carnitas 2.99
Barbacoa 1.99
Asada Fries 0.99

Now let's change things around a bit more. This time we will change two aspects of our sql as well.

  1. Select clause - Specify that the price will be displayed first and the dish second.
  2. Order by clause - Specify that the results will be sorted first by price (descending) and then by dish name
Select 
  Price
  ,Dish
From Menu
Where Price < 3
Order by 
  Price desc
  ,Dish asc
Price Dish
2.99 Carnitas
2.99 Noodles!
2.99 Xiaolongbao
1.99 Barbacoa
1.99 Milkshake
1.99 Water
0.99 Asada Fries
0.99 Lasagna
0.99 Vanilla Wafers

We can see that we got exactly what we asked for. Price is displayed first and sorted in descending order. Dish is displayed second and used as a secondary sort criteria.

What would happen if we changed this query so that the dish was sorted first?

Next
Prev