Last row in select question

[Kurzman]

DECLARE @LastID int
DECLARE @RowsToSelect int

SET ROWCOUNT @RowsToSelect
SELECT @LastID = [id] FROM MyTable ORDER BY [id]
SET ROWCOUNT 0

I think that value of @LastID will be equals to [id] in the last row
of select(i.e. row with number @RowsToSelect). But I don't understand
why it's true. For example, @LastID not necessarily equals to last row
value in the same select without 'ORDER BY [ID]'.

[Erland Sommarskog]

Kurzman (ma*@virtuman.com) writes:

DECLARE @LastID int
DECLARE @RowsToSelect int

SET ROWCOUNT @RowsToSelect
SELECT @LastID = [id] FROM MyTable ORDER BY [id]
SET ROWCOUNT 0

I think that value of @LastID will be equals to [id] in the last row
of select(i.e. row with number @RowsToSelect). But I don't understand
why it's true.
Books Online says:

If the SELECT statement returns more than one value, the variable is
assigned the last value returned.

So your observation is correct.
For example, @LastID not necessarily equals to last row value in the same
select without 'ORDER BY [ID]'.

How do you know that it is not the last value? Without an ORDER BY
clause, SQL Server is free to return the rows in any order, so any value
of [id] is correct. Recall that logically tables are unordered sets of
data, so there is no first or last value in a table.

You must always specify an ORDER BY clause to impose a certain order.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp