BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Basic T-SQL Queries

The eighth part of the SQL Server Programming Fundamentals tutorial describes the use of the SELECT statement of the structured query language. This command allows the creation of database queries that return table rows that meet specified criteria.

Using TOP and ORDER BY

A very useful technique is to combine the use of ORDER BY with the use of the TOP clause. This allows the creation of queries such as the following, which retrieves the newest nine customers based on the CreatedDate column value.

SELECT TOP 9
    FirstName,
    LastName,
    BusinessName,
    CreatedDate
FROM
    Customers
ORDER BY
    CreatedDate DESC

This does cause an interesting effect. In the case of the above query, the ninth and tenth newest customers actually have the same CreatedDate value. In the query, SQL Server arbitrarily selects one of these for inclusion in the results and discards the other. This may be acceptable in some cases but often you will want to retrieve additional results where such duplication exists. This is possible if you add the WITH TIES clause to the TOP keyword. Now if there are duplicates that would normally be discarded, they are included in the result set.

The final sample statement shows the WITH TIES clause. Although the top nine results are requested, a duplicate means that the query returns ten rows.

SELECT TOP 9 WITH TIES
    FirstName,
    LastName,
    BusinessName,
    CreatedDate
FROM
    Customers
ORDER BY
    CreatedDate DESC
3 August 2008