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.

On Dec 31, 1:50pm, metaperl <metap...@gmail.com> wrote:

>
> 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?

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

--
Gert-Jan


metaperl wrote:
>
> 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.

"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