Re: top n problem

From: Delbert Glass (delbert_at_noincoming.com)
Date: 02/13/04 

Date: Fri, 13 Feb 2004 17:31:59 -0600

Nevertheless, TOP without an ORDER BY is defined.
It is defined as giving you a limited amount of arbitrary rows.

Bye,
Delbert Glass

"Steve Kass" <skass@drew.edu> wrote in message
news:urJGaOo8DHA.2560@TK2MSFTNGP09.phx.gbl...
> Delbert Glass wrote:
>
> >>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?
> >
> >-----
> >
> >Following is some info from BOL.
> >
> >
> The crucial word: "some". My copy of Books Online also says:
>
> If the query has no ORDER BY clause, the order of the rows is
arbitrary.
>
> "Arbitrary" is good enough for me. Perhaps Vlad could have said the
> result of a SELECT TOP query without ORDER BY is undefined instead of
> saying TOP ... is undefined, but I think he was clear enough. Just
> mentioning something that some operator does does not define the
> operator. "A car burns fuel." Have I defined a car?
>
>
> SK
>
>
>
> >Look at what that second line says TOP does.
> >Look at that introductory phrase of the first sentence of the last
> >paragraph.
> >
> >Limiting Result Sets Using TOP and PERCENT
> >The TOP clause limits the number of rows returned in the result set.
> >
> >TOP n [PERCENT]
> >
> >n specifies how many rows are returned. If PERCENT is not specified, n is
> >the number of rows to return. If PERCENT is specified, n is the
percentage
> >of the result set rows to return:
> >
> >TOP 120 /*Return the top 120 rows of the result set. */
> >TOP 15 PERCENT /* Return the top 15% of the result set. */.
> >If a SELECT statement that includes TOP also has an ORDER BY clause, the
> >rows to be returned are selected from the ordered result set. The entire
> >result set is built in the specified order and the top n rows in the
ordered
> >result set are returned.
> >
> >Bye,
> >
> >Delbert Glass
> >
> >
> >
> >"Vlad Vissoultchev" <wqweto@nospam.myrealbox.com> wrote in message
> >news:u6BpbQd8DHA.712@tk2msftngp13.phx.gbl...
> >
> >
> >>delbert,
> >>
> >>the point here is that TOP n without ORDER BY is undefined -- undefined
as
> >>in NULL :-))
> >>
> >>your sample looks more like a TOP 5 with ORDER BY
<random_generator_here>
> >>
> >>the fact that you can get away without a syntax error from that parser
> >>
> >>
> >when
> >
> >
> >>ORDER BY is omitted is a "feature" that some college grad though was
cool
> >>
> >>
> >to
> >
> >
> >>propose to its PM at microsoft back some time ago (disclaimer: on second
> >>reading, this scenario might be very far from the truth).
> >>
> >>suppose you could use CASE WHEN without a THEN :-))
> >>
> >>cheers,
> >></wqw>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>

Relevant Pages

  • Re: top n problem
    ... Delbert Glass wrote: ... If the query has no ORDER BY clause, the order of the rows is arbitrary. ... "A car burns fuel." ... >n specifies how many rows are returned. ...
    (microsoft.public.sqlserver.programming)
  • Re: top n problem
    ... Delbert Glass wrote: ... >>It is defined as giving you a limited amount of arbitrary rows. ... The behavior of the FLOOR function is implementation-dependent. ...
    (microsoft.public.sqlserver.programming)
  • Re: top n problem
    ... > It is defined as giving you a limited amount of arbitrary rows. ... Compare that to what I said earlier in the thread: ... Delbert Glass ...
    (microsoft.public.sqlserver.programming)