Re: top n problem
From: Delbert Glass (delbert_at_noincoming.com)
Date: 02/13/04
- Next message: Delbert Glass: "Re: top n problem"
- Previous message: AndrewV: "Re: How to best use clustered index?"
- In reply to: Vlad Vissoultchev: "Re: top n problem"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 13 Feb 2004 17:23:38 -0600
Any abuse that is occurring
would be from using TOP at all
since top is not standard SQL -- not from
using TOP without ORDER BY
which is clearly allowed
by the following passage from BOL:
>TOP n [PERCENT]
>
>Specifies that only the first n rows are
>to be output from the query result set.
>n is an integer between 0 and 4294967295.
>If PERCENT is also specified, only the
>first n percent of the rows
>are output from the result set.
>When specified with PERCENT,
>n must be an integer between 0 and 100.
>
>If the query includes an ORDER BY clause,
>the first n rows (or n percent of rows)
>ordered by the ORDER BY clause are output.
>If the query has no ORDER BY clause,
>the order of the rows is arbitrary.
>
>WITH TIES
>
>Specifies that additional rows be returned
>from the base result set with the same value
>in the ORDER BY columns appearing as
>the last of the TOP n (PERCENT) rows.
>TOP ...WITH TIES can only be specified
>if an ORDER BY clause is specified.
--- Perhaps you'll find the following helpful. Just because ALL often should be included in queries that contain UNION -- but often is inadvertitely omitted, does not mean UNION without ALL is undefined. Likewise: Just because ORDER BY often should be included in queries that contain TOP -- but often is inadvertitely omitted, does not mean TOP without ORDER BY is undefined. Bye, Delbert Glass "Vlad Vissoultchev" <wqweto@nospam.myrealbox.com> wrote in message news:uMxyjnn8DHA.3880@tk2msftngp13.phx.gbl... > > > the point here is that TOP n without ORDER BY is undefined > > > > Pray tell, where does it say that in the documention? > > Are you just repeating mis-information, or did you make that up yourself? > > ok, > > [ab]use it as you wish. next you can start rewriting those set-oriented > selects using cursors -- it's legal T-SQL syntax :-)) > > my point here is that everytime a TOP n w/o ORDER BY is used should be > considered a gross hack. there are numerous examples of dubious > selects/updates that [seem to] work but might just stop to under different > conditions. > > btw, i get the same gut feeling when a see an IDENTITY property used but > this is OT by far and large... > > cheers, > </wqw> > >
- Next message: Delbert Glass: "Re: top n problem"
- Previous message: AndrewV: "Re: How to best use clustered index?"
- In reply to: Vlad Vissoultchev: "Re: top n problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
