Select Distinct (was top N or rewriting MAX)

From: AWILSON <awilson_at_rci.rogers.com>
Date: 1997/01/16
Message-ID: <32DEC3C1.7393@rci.rogers.com>#1/1

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.


======================================================================

Received on Thu Jan 16 1997 - 00:00:00 CST