Re: Select Distinct (was top N or rewriting MAX)

I think you are confusing definition with implementation. By definition ORDER BY "orders rows returned by the statement" and SELECT DISTINCT "returns only one copy of each set of duplicate rows selected". As you can see they are completely different by definition. By implementation they both are using sort and therefore SELECT DISTINCT comes also ordered. Your solution to the original question takes advantage of such implementation. You can not assume that ORACLE will not change it in the future (most likely it will not), e.g. will start sorting in descending order.
Now about your solution. It works only if table has no duplicates. For example: table has 15 one column rows. Five of them have value 1 and 10 have value 2. Your solution will return only two rows: 1 and 2 while I need five rows with the value 1 and 5 with the value 2.

Solomon.Yakobson_at_entex.com

In article <32DEC3C1.7393_at_rci.rogers.com>,   AWILSON <awilson_at_rci.rogers.com> wrote:
>
> Pardon my jumping in again,
>
> I answered the original post (top N rows in a table) by saying
>
> 1. Create a view as select distinct col from table
> 2. select col from view where rownum <= N
>
> The above delivers the N lowest distinct values of col (If col is
> numeric you can easily get the top values by selecting distinct (Y -
> col) where Y is any value larger than max(col).
>
> In this instance, select distinct seems to do an implicit 'order by' on
> the distinct values of 'col'. It is a bit of a kluge, but seems to work
> OK.
>
> This raises a couple of interesting points.
>
> How does Select Distinct work - ie what mechanism does it use to ensure
> distinctness?
> Can it be relied upon to work the same way in future?
>
> ======================================================================
> The above reflect my personal views and in no way are connected with
> whomsoever I may be working for at this particular point in time.
>
> Alan Wilson Help fight continental drift.
> ======================================================================

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet