This page has 0 threads | Add post

Sorting in SQL Server Queries

Written by Andrew Gould

In this tutorial

The ORDER BY Clause
Sorting Queries Using Column Names
Sorting on Multiple Columns
Sorting Using Non-Selected Columns
Sorting Queries Using Aliases
The TOP Keyword
Using Sorting to Change the Top Records
Including Tied Records
Showing the Top Records as a Percentage

This tutorial teaches you how to sort the results of basic queries in Microsoft SQL Server, as well as how to use the TOP keyword to pick out only certain rows.

The ORDER BY Clause

You can sort the results of a query by adding the ORDER BY clause to a SELECT statement. If you've read our blog on basic SQL queries you'll remember that ORDER BY is the last of the six main keywords in a SQL query. If you haven't read the previous blog, here's a quick reminder of the order of keywords:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

Sorting Queries Using Column Names

To sort the results of a query you can simply add a column name to the ORDER BY clause:

Adding the FilmName field to the ORDER BY clause sorts the query results in ascending order of film title.

By default, a column will be sorted in ascending order. You can add the ASC keyword to make this more obvious:

The ASC keyword is optional but makes the query more readable.

You can sort a column in descending order by adding the DESC keyword after the column name:

Adding the DESC keyword sorts a column in descending order.

Sorting on Multiple Columns

You can sort your query on multiple columns by listing extra column names separated with commas:

Here the films are sorted in descending order of release date. Where multiple films share a release date they are sorted in ascending order of name.

Sorting Using Non-Selected Columns

You don't have to include a column in the SELECT list in order to use it in the ORDER BY clause:

The results of this query are sorted so that the most successful Oscar winners appear at the top, even though that field isn't shown in the query.

Sorting Queries Using Aliases

If you've used aliases to replace long column names in the SELECT list you can also use them in the ORDER BY clause:

Here we've used the alias to sort the films in descending order of duration.

Using an alias is also a useful way to sort on a calculated column.

The TOP Keyword

You can add the TOP keyword followed by a number immediately after the SELECT keyword in a query:

You can use any whole number after the word TOP to return that number of items.

Using Sorting to Change the Top Records

To make the TOP keyword more useful you can apply sorting to the results to change which records appear at the top of the list. The example below sorts the films in descending order of running time and then selects the top five films from the results:

This query shows the top five longest films.

To show the bottom records from the results, simply change the order of the sorting:

Here we've changed the order of the records so that the shortest films appear at the top of the list.

Including Tied Records

When you use the TOP keyword your query will return the number of records you have asked for, even if there are more records with a matching value in the sorted column. You can display these extra records by adding the WITH TIES keywords to your statement:

We only asked to see the TOP 5 records but because there was one more film with the same running time as the fifth we see that this is included in the results.

Showing the Top Records as a Percentage

Rather than asking to see a specific number of records you can ask to see a percentage of the total records returned by a query. To do this you just need to add the PERCENT keyword to your statement:

The table of films contains 260 records in total. When we ask for the TOP 10 PERCENT we return to top 26 records based on the sort order of the results.

When you use criteria in a query the PERCENT keyword returns a percentage of the records that are returned after the criteria have been applied.

Previous
tutorial

Creating databases

Previous
part

Getting Started with SQL Queries

Top of page

Next
part

Referring to Tables in the FROM Clause

Next
tutorial

WHERE criteria (version A)

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Writing queries