SELECT TOP 1
-
Posted on May 23, 2010 by Derek Dieter
-
4
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:
[cc lang=”sql”]
SELECT
*
FROM SalesLT.SalesOrderHeader soh
WHERE TotalDue =
(
SELECT MAX(TotalDue)
FROM SalesLT.SalesOrderHeader
WHERE SalesOrderID = soh.SalesOrderID
)
[/cc]
However, using the SELECT..TOP 1 method of querying, you can perform the same query using the following notation.
[cc lang=”sql”]
SELECT TOP 1
*
FROM SalesLT.SalesOrderHeader
ORDER BY TotalDue DESC
[/cc]
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:
[cc lang=”sql”]
SET ROWCOUNT 1
SELECT
*
FROM SalesLT.SalesOrderHeader
ORDER BY TotalDue DESC
[/cc]
- Comments (RSS)
- Trackback
- Permalink
Hi,
Please forgive what is most likely a silly question, but I am an SQL newbie.
What is the significance of :
“WHERE SalesOrderID = soh.SalesOrderID”?
Is this not just like saying, “where A = A”, or “where 1 = 1”?
It doesn’t seem to relate to joining tables as the only table in the query seems to be SOH?
The soh. refers to the outer query so that the two queries are correlated.
Great explanation. Thanks!
SELECT TOP 1 * FROM CR_RATING_SUMM CRS
WHERE DOPA_YEAR < 2011 AND YTOT =
(SELECT MAX(YTOT) FROM CR_RATING_SUMM WHERE SRNO = CRS.SRNO) ORDER BY SRNO,DOPA_YEAR;
The code is giving error message.
I would generally prefer to do the nested Select option as it takes only half the time.
Using the Top 1 Method may be convenient for Coding but does not pay off when it comes to runtime efficiency…
Always ask the query analyzer first 😉
Just to add a Stat here:
On my Computer the
Nested Select has a Cost of 0.00677
whereas the
Top 1 Select has a cost of 0.01485
because sorting the table alone costs you 0.01154. Sorting is very Time and Processor Intensive as you need to compare two values over and over again which means you go through your result set several times. So be aware of what you sort!
A tip for those who would like to compare two methods of Querying: Type your two Statements in the Query and look at the execution plan. It will tell you which query took how much of the whole Batch. In my example the Nested Select took 31% of the Batch and the Top 1 took 69%…
Brenda,
select max(date),event
from eventTable
group by event
or
select top 1 date, event
from eventTable
group by event
order by date desc
How can I find the most recent date of a repeating event?
