More than 10 records in the TOP 10

Using the TOP keyword, it is possible to retrieve more records than you were expecting.

Consider the following table, called "tblWidgetsSold":

The following query will return exactly the number of records that you might expect (two):

SELECT TOP 2 Country, WidgetsSold
FROM tblWidgetsSold
ORDER BY WidgetsSold DESC

The two records that are returned by this query are:

OK, no surprises there. But what if the number of widgets sold in New Zealand is also 32768 ?  How would the SQL compiler know which record to pick for the number 2 position?

The answer is that it doesn't. The exact results vary between implementations of SQL.

In MS Access 2000, you would get three records:

SQL Server will return two records, with no specific criteria defining which record will be picked in the case of a tie.

SQL Server can be forced to return all the records which tie for 2nd place, using the following syntax:

SELECT TOP 2 WITH TIES Country, WidgetsSold 
FROM tblWidgetsSold
ORDER BY WidgetsSold DESC

So, how do you find out whether there is a tie for second place, if the implementation of SQL you are using does not support WITH TIES?

By selecting the TOP 3 records, and comparing the number of widgets sold for the 2nd and 3rd records. If they are the same then you have a tie.

If you are using MS Access, and you don't want to see all the records that tied for second place, then add an extra field to the ORDER BY clause.

SELECT TOP 2 Country, WidgetsSold
FROM tblWidgetsSold
ORDER BY WidgetsSold DESC, Country

If the ORDER BY clause includes a field which does not have any duplicate values, then there cannot be a tie.

Adelle.