ThoughtSpot acquires Mode to define the next generation of collaborative BI >>Learn More

#### Mode Studio

The Collaborative Data Science Platform

# SQL ORDER BY

Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.

In this lesson we'll cover:

## Sorting data with SQL ORDER BY

Once you've learned how to filter data, it's time to learn how to sort data. The `ORDER BY` clause allows you to reorder your results based on the data in one or more columns. First, take a look at how the table is ordered by default:

``````SELECT * FROM tutorial.billboard_top_100_year_end
``````

Now let's see what happens when we order by one of the columns:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
ORDER BY artist
``````

You'll notice that the results are now ordered alphabetically from a to z based on the content in the `artist` column. This is referred to as ascending order, and it's SQL's default. If you order a numerical column in ascending order, it will start with smaller (or most negative) numbers, with each successive row having a higher numerical value than the previous. Here's an example using a numerical column:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
ORDER BY year_rank
``````

If you'd like your results in the opposite order (referred to as descending order), you need to add the `DESC` operator:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
ORDER BY year_rank DESC
``````

## Practice Problem

Write a query that returns all rows from 2012, ordered by song title from Z to A.

Try it out See the answer

## Ordering data by multiple columns

You can also order by mutiple columns. This is particularly useful if your data falls into categories and you'd like to organize rows by date, for example, but keep all of the results within a given category together. This example query makes the most recent years come first but orders top-ranks songs before lower-ranked songs:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank <= 3
ORDER BY year DESC, year_rank
``````

You can see a couple things from the above query: First, columns in the `ORDER BY` clause must be separated by commas. Second, the `DESC` operator is only applied to the column that precedes it. Finally, the results are sorted by the first column mentioned (`year`), then by `year_rank` afterward. You can see the difference the order makes by running the following query:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank <= 3
ORDER BY year_rank, year DESC
``````

Finally, you can make your life a little easier by substituting numbers for column names in the `ORDER BY` clause. The numbers will correspond to the order in which you list columns in the `SELECT` clause. For example, the following query is exactly equivalent to the previous query:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank <= 3
ORDER BY 2, 1 DESC
``````

Note: this functionality (numbering columns instead of using names) is supported by Mode, but not by every flavor of SQL, so if you're using another system or connected to certain types of databases, it may not work.

When using `ORDER BY` with a row limit (either through the check box on the query editor or by typing in `LIMIT`), the ordering clause is executed first. This means that the results are ordered before limiting to only a few rows, so if you were to order by `year_rank`, for example, you can be sure that you are getting the lowest values of `year_rank` in the entire table, not just in the first 100 rows of the table.

## Practice Problem

Write a query that returns all rows from 2010 ordered by rank, with artists ordered alphabetically for each song.

Try it out See the answer

You can "comment out" pieces of code by adding combinations of characters. In other words, you can specify parts of your query that will not actually be treated like SQL code. It can be helpful to include comments that explain your thinking so that you can easily remember what you intended to do if you ever want to revisit your work. Commenting can also be useful if you want to test variations on your query while keeping all of your code intact.

You can use`--` (two dashes) to comment out everything to the right of them on a given line:

``````SELECT *  --This comment won't affect the way the code runs
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
``````

You can also leave comments across multiple lines using `/*` to begin the comment and `*/` to close it:

``````/* Here's a comment so long and descriptive that
it could only fit on multiple lines. Fortunately,
it, too, will not affect how this code runs. */
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
``````

## Practice Problem

Write a query that shows all rows for which T-Pain was a group member, ordered by rank on the charts, from lowest to highest rank (from 100 to 1).

Try it out See the answer

## Practice Problem

Write a query that returns songs that ranked between 10 and 20 (inclusive) in 1993, 2003, or 2013. Order the results by year and rank, and leave a comment on each line of the `WHERE` clause to indicate what that line does

Try it out See the answer

## What's next?

Congrats on completing the Basic SQL tutorial!

You may find that your skills are limited, though. If you want to do things like aggregate data across entire columns or merge multiple datasets together, check out the Intermediate SQL section of this tutorial.