Posted by David Portas on 12/31/07 19:42

"metaperl" <metaperl@gmail.com> wrote in message
news:bd9b28eb-22ad-4146-93e3-4dc8fdf4907c@w47g2000hsa.googlegroups.com...
> If I do
>
> SELECT TOP 25 * FROM table ORDER BY id
>
> does it select any 25 records and then order them
>
> or does it SELECT all the records, order them by ID then return the
> first 25?
>
> I'm guessing the former (based on some comparative SELECTs) and think
> I need to do this:
>
> SELECT TOP 25
> (SELECT * FROM table ORDER BY id)
>
> to get what I want.


Books Online is your friend:

"If the query includes an ORDER BY clause, the first expression rows, or
expression percent of rows, ordered by the ORDER BY clause are returned. If
the query has no ORDER BY clause, the order of the rows is arbitrary."

http://msdn2.microsoft.com/en-us/library/ms189463.aspx

This is a very silly syntax (invented by Microsoft and not part of standard
SQL) because it means the ORDER BY serves a double purpose, which leads to
confusion all round.

--
David Portas

[Back to original message]