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

Yes, duplicates weren't mentioned explicitly. But they are real life and the original question is real life question. E.g. question how to select top 10 customers based on spent $ amount already assumes possibility of duplicates (two or more customers spent same $ amount), etc.

Solomon.Yakobson_at_entex.com

In article <32E3D378.B2F_at_rci.rogers.com>,   awilsonATrci.rogers.com wrote:
>
> 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.
> ======================================================================

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