Re: top n problem

From: Steve Kass (skass_at_drew.edu)
Date: 02/13/04 

Date: Fri, 13 Feb 2004 17:48:37 -0500

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
    ... It is defined as giving you a limited amount of arbitrary rows. ... Delbert Glass ... >>The TOP clause limits the number of rows returned in the result set. ... >>n specifies how many rows are returned. ...
    (microsoft.public.sqlserver.programming)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)
  • Re: top n problem
    ... >to be output from the query result set. ... >ordered by the ORDER BY clause are output. ... >Specifies that additional rows be returned ... does not mean UNION without ALL is undefined. ...
    (microsoft.public.sqlserver.programming)
  • [Info-ingres] Wrong results returned from outer join
    ... " Outer joins specified in the from clause are not the same as joins ... while the where clause specifies restrictions to be applied to the sources ... Still the script returns all 2 rows. ... ON COMMIT PRESERVE ROWS WITH NORECOVERY ...
    (comp.databases.ingres)
  • RE: error 8120 not contained aggregate function error
    ... GROUP BY Clause ... Specifies the groups into which output rows are to be placed and, ... aggregate functions are included in the SELECT clause, ... > from CCList inner join CCThemes on CCThemes.ThemeID=CCList.CalculationTheme ...
    (microsoft.public.sqlserver.programming)