5

What does TOP 1 mean in an sql query?

SELECT TOP 1 RequestId 
FROM PublisherRequests
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
mohammad reza
  • 3,292
  • 6
  • 29
  • 39
  • 1
    It means take only the first n rows. You need an "order by" to define what the first rows will be. – dotjoe May 20 '09 at 14:40

6 Answers6

17

I disagree with "The order will be defined based on the clustered key in that table."

SQL Server Books Online is quite explicit: If the query has no ORDER BY clause, the order of the rows is arbitrary

a repro demonstrating that this is not always the case: Without ORDER BY, there is no default sort order.

brianpeiris
  • 10,735
  • 1
  • 31
  • 44
A-K
  • 16,804
  • 8
  • 54
  • 74
10

It will select the first row from the PublisherRequests table.

EDIT: [The order will be defined based on the clustered key in that table - This statement is incorrect]. Actually, according to Alex's findings, and according to BOL, the order of the rows will be arbitrary.

Reference can be found here.

Kirtan
  • 21,295
  • 6
  • 46
  • 61
  • 1
    not necessarily true. I posted a repro demonstrating that this is not always the case: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/without-order-by-there-is-no-default-sort-order.aspx – A-K May 20 '09 at 13:45
  • Also see Conor Cunningham's (MS Query Optimisation Team) blog entry on the same topic http://blogs.msdn.com/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx – GilaMonster May 23 '09 at 15:41
9

The query in the example will return the first RequestID from the table PublisherRequests.
The order of the results without an Order By clause is arbitrary. So, your example will return an arbitrary RequestID (i.e. the first RequestID in an arbitrarily ordered list of RequestIDs).
You can change the order by defining an Order By.
For example, to get the last entered ID, you can write

Select Top 1 RequestID
From PublisherRequests
Order By RequestID Desc

Updated to include corrected order information from @Kirtan Gor and @AlexK

Community
  • 1
  • 1
a programmer
  • 959
  • 4
  • 8
3

The TOP can be used to return as many rows as necessary, or a percentage of the total rows included - for more information, see http://msdn.microsoft.com/en-us/library/ms189463.aspx

thecoop
  • 45,220
  • 19
  • 132
  • 189
2

It means only return the top 1 row, i.e. the first row.

David M
  • 71,481
  • 13
  • 158
  • 186
0

It limits the number of rows returned from the query to just 1. Its the same as Limit 1 in MySQL.

corymathews
  • 12,289
  • 14
  • 57
  • 77