Learn T-SQL Querying

SELECT TOP 1 with ORDER BY

A very common way to return the maximum or minimum row in a set is to perform a SELECT TOP 1 with an ORDER BY clause. The problem with this pattern is that it may result in an unnecessary sort operation. SQL Server will need to sort all the rows to order them by the desired column, but then return only the first (or last) row in the set. In some cases, it is more efficient to find the minimum or maximum value first, then select the row that is equal to this value.

Let's look at an example from the AdventureWorks database.

The following query returns the row with the highest sub-total from the Sales.SalesOrderHeader table:

SELECT TOP 1 soh.CustomerID, SalesPersonID, SubTotal, OrderDate, cust.LastName as CustomerLastName, ...