What does TOP 1 mean in an sql query?

Question

What does TOP 1 mean in an sql query?

SELECT TOP 1 RequestId 
FROM PublisherRequests

Answer 1

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.

Answer 2

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

Answer 3

Your query in specific will return the first row of the table. If you were to add a WHERE clause, it would return the first row of those results. Example:

SELECT Abbr FROM States WHERE Abbr LIKE 'T%'; -- ['TN', 'TX']
SELECT TOP 1 Abbr FROM States WHERE Abbr LIKE 'T%'; -- ['TN']

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

BOL 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.

Answer 8

If you want a given number of results, you can also use the limit keyword,

i.e.

Select RequestID From PublisherRequests Order By RequestID Desc LIMIT 1