Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3

SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
Will the statement below, always return the first record of the same
stand-alone SELECT statement as below:
SELECT * FROM ... ORDER BY Field1, Field2, Field3
Thanks,
JayYes, assuming you use the ORDER BY clause and the data remains constant.
Insert a new row, and it may be the new top result.
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>|||Didn't you see the contrary example that Razvan posted?
http://groups.google.com/group/micr...3752b9548322706
David Portas
SQL Server MVP
--|||In SQL Server 2005, we are a bit more consistent with TOP + ORDER BY
semantics than perhaps some previous releases.
Here are the basic rules:
1. ORDER BY determines the presentation order for the _output_ of a query.
2. Within the same select block, an ORDER BY implies that TOP returns the
TOP N rows (not necessarily in a specific order).
3. ORDER BY in subselects or views does *not* guarantee the output of a
containing query.
So, for TOP N... ORDER BY ... with no containing select block, both the set
and the order are guaranted.
Within a subquery, TOP N ... ORDER BY guarantees the set but not the output
order (you need a top-level ORDER BY to guarantee output order).
Conor Cunningham
SQL Server Query Optimization Team
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>

Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Fie

Many thanks David and Razvan, I've rated your posts as helpful.
--
Adam J Warne, MCDBA
"David Portas" wrote:

> Not necessarily. We don't know what other columns are involved or what
> the keys are. You can only guarantee that you'll get the same
> particular row from the top of the second query if the three ORDER BY
> clolumns are UNIQUE. Use TOP 1 WITH TIES if the ORDER BY criteria isn't
> unique.
> --
> David Portas
> SQL Server MVP
> --
>>The WITH TIES clause is new to me should be
There is even a web log named toponewithties :)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131539036.434912.103710@.g44g2000cwa.googlegroups.com...
> Thanks for the replies. The WITH TIES clause is new to me should be
> very helpful. Thanks.
>