There are many good uses of the SELECT TOP 1 method of querying. Essentially, the select top 1 method is used to find the min or max record for a particular column’s value. There is some debate as to whether this is the ‘correct’ method of querying, however it should be known that this method does not break any guidelines and is supported by all standards of SQL.

The TOP 1 means to only return one record as the result set. which record is returned, depends on the column that is specified in the order by clause. If you want to find the record with the minimum value for a particular column, you would query the record with the ORDER BY being ascending (ASC). If you want to find the maximum record with that value, you would query it with the ORDER BY descending (DESC).

For example, say you wanted to find the record in a table for a customer that has the largest order. Traditionally, the way to do this would be to find the maximum order value then join back on the table in order to find the record. Like the following:

SELECT
*
FROM SalesLT.SalesOrderHeader soh
WHERE TotalDue =
(
	SELECT MAX(TotalDue)
	FROM SalesLT.SalesOrderHeader
	WHERE SalesOrderID = soh.SalesOrderID
)

However, using the SELECT..TOP 1 method of querying, you can perform the same query using the following notation.

SELECT TOP 1
	*
FROM SalesLT.SalesOrderHeader
ORDER BY TotalDue DESC

One of the major differences is that the SELECT..TOP 1 will only ever return one record. Where as joining against the max has a possibility of returning more than one record. This is very important when setting variable in SQL Server. This is because setting a variable in SQL Server is a scalar operation (meaning only one result is allowed to be returned).

Another way to perform this method which I would argue as being faster than both methods, is to use the SET ROWCOUNT 1.

It is even more debatable as to whether SET ROWCOUNT is proper SQL for this method of querying, however especially regarding SQL SERVER Pagination, ROWCOUNT is the quickest method of all. Here is the alternative method using ROWCOUNT for the above examples:

SET ROWCOUNT 1

SELECT
	*
FROM SalesLT.SalesOrderHeader
ORDER BY TotalDue DESC

• select top 1
• select top 1 *
• select top1
• select top 1 from
• select top 1 1