Using ORDER BY to Sort in SQL Server Queries
Part two of a two-part series of blogs

If your SQL queries return lots of records you'll probably want to sort the results in some kind of order. This blog teaches you how to use the ORDER BY clause to do exactly that!

  1. Sorting in SQL Server Queries
  2. Finding the Top Records in a Query (this blog)

This article is part of our complete SQL tutorial.  You might also be interested in our introduction to SQL or advanced SQL training courses.

Posted by Andrew Gould on 23 July 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Finding the Top Records in a Query

You can use the TOP keyword in a SQL query to return a specified number of items from a table.  You can use the ORDER BY clause to affect which items you will return.

The TOP Keyword

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

Using TOP keyword

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:

Showing top 5 longest

This query shows the top five longest films.

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

Bottom records

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:

With ties

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:

Showing top ten percent

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.

  1. Sorting in SQL Server Queries
  2. Finding the Top Records in a Query (this blog)
This blog has 0 threads Add post