TOP 100 PERCENT - Why?

"Jordan S." <A.DeleteThis@B.com> wrote in message
news:OJ4NZcXVJHA.3708@TK2MSFTNGP03.phx.gbl...
>I just found this query in a legacy datase.
>
> When, if ever, does "TOP 100 PERCENT" ever make sense? Why do it?

Never. However, the query engine is usually smart enough to ignore the top
directive in such cases.
 >> Stay informed about: TOP 100 PERCENT - Why? 

This is standard practice if you're using SQL Views, because you can't sort
them, if you try it tells you it can only be sorted if using top.

So by doing a top 100 percent you sort the whole table.... its silly but
those are the rules.

Steve



"Jordan S." <A RemoveThis @B.com> wrote in message
news:OJ4NZcXVJHA.3708@TK2MSFTNGP03.phx.gbl...
>I just found this query in a legacy datase.
>
> When, if ever, does "TOP 100 PERCENT" ever make sense? Why do it?
>
> SELECT TOP 100 PERCENT LastName FROM
> (
> SELECT LastName FROM People1
> UNION
> SELECT LastName FROM People2
> ) Alias
> ORDER BY 1
>
>
 >> Stay informed about: TOP 100 PERCENT - Why? 

On 3 Dec, 20:43, "steve" <steve_le... DeleteThis @nospam.hotmail.com> wrote:
> This is standard practice if you're using SQL Views, because you can't sort
> them, if you try it tells you it can only be sorted if using top.
>
> So by doing a top 100 percent you sort the whole table.... its silly but
> those are the rules.
>

Not silly at all. The silly thing is to assume that by using TOP 100
PERCENT in a view you will determine the ordering of some other query
without ORDER BY that uses the view. That assumption is incorrect
because the ordering of any other query *without* the ORDER BY clause
is undefined - even if it uses a view that contains ORDER BY.
Therefore it never makes sense to use TOP 100 PERCENT... ORDER BY in a
view.

Microsoft is partly to blame for this confusion because they invented
the daft "TOP" syntax, which assigns a double meaning to the ORDER BY
clause. ORDER BY is used in views only to select the subset of rows
required by the TOP clause. It has nothing to do with ordering the
results of queries that use the view.

--
David Portas
 >> Stay informed about: TOP 100 PERCENT - Why?