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

From: AWILSON <awilson_at_rci.rogers.com>
Date: 1997/01/20
Message-ID: <32E3D378.B2F@rci.rogers.com>#1/1

Solomon.Yakobson_at_entex.com wrote:
>
> I think you are confusing definition with implementation...

No confusion on my part, I understand the 'solution' was a kluge.

> 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.
>

Duplicate values weren't mentioned in the original post... Out of the 10 rows with the value '2' which 5 would you want? Seems a bit arbitrary to me as to which 5 you'd actually get as they could be different between queries, that's why I assumed non-duplicates.

If it's duplicates you want, how about rating other columns (if there are any) using a decode and subtracting them from the original value?

eg:
select distinct ((100*(10 - col)) - (decode(col2,'A',10,'B', 20, 30),

       col1
  from t1;

or something along those lines, maybe using the rowid.

Just a suggestion.

---

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 Mon Jan 20 1997 - 00:00:00 CST