SELECT TOP * ORDER BY question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

metaperl - 31 Dec 2007 18:50 GMT

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.

Reply to this Message

metaperl - 31 Dec 2007 19:17 GMT

> SELECT TOP 25
> (SELECT * FROM table ORDER BY id)

actually the above is invalid syntax. how can I get MS-SQL to give me
the 25 records which have the lowest id instead of 25 random records
ordered by id?

Reply to this Message

Gert-Jan Strik - 31 Dec 2007 19:20 GMT

metaperl,

Logically, this statement will select all rows from "table", sort them
on column id, select the first 25 of them, and return these rows.

If you would want the other behavior that you describe (which is
unlikely), you would have to write this:

SELECT *
FROM (
SELECT TOP 25 *
FROM table
ORDER BY NEWID()
) AS T
ORDER BY T.id

Signature

Gert-Jan

> If I do
>
[quoted text clipped - 12 lines]
>
> to get what I want.

Reply to this Message

David Portas - 31 Dec 2007 19:42 GMT

> If I do
>
[quoted text clipped - 12 lines]
>
> 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.

Signature

David Portas

Reply to this Message