Re: Anyone have a better way??

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/07/15
Message-ID: <33cb7775.23446637@www.sigov.si>#1/1

On Tue, 15 Jul 1997 10:45:59 +0100, Jensen Langford - Sun DBA <jensen.langford_at_uk.sun.com> wrote:

>Phil
>
>I may be missing the point here but I get the results you are looking
>for using;
>SQL> select * from jens;
>
> DEV QUALITY OWNER
>---------- ---------- ----------
> 1 8 ABC
> 1 8 BCA
> 1 8 AAA
> 1 5 BBB
> 1 5 CCC
> 1 4 BAA
> 2 9 AAA
> 2 9 CCC
> 2 1 BCA
> ...[SNIP]...
>SQL> select dev, max(QUALITY) from jens group by DEV;
>
> DEV MAX(QUALITY)
>---------- ------------
> 1 8
> 2 9
> 3 4
> 19 9
> 20 4
>
>Let me know if this is correct.
>If you need to select other columns ie misc1,2 etc put a MAX/MIN around
>the column so the group by still works.
>
>Jens.

If Phil wanted to select only this two columns then your answer would be correct (and in that case the question would be too trivial).

Hovewer, he wants to select other columns as well and in that case your suggestion to use MIN/MAX function on those columns is wrong since each returned row would then not represent a single row from a table. For example, if you use

select DEV, max(QUALITY) QUALITY, max(OWNER) OWNER   from jens group by DEV;

you'll get (for DEV = 1)

       DEV QUALITY OWNER
---------- ---------- ----------

         1 8 CCC which is incorect, because owner for DEV=1 nad QUALITY = 8 can only be 'ABC', 'BCA' or 'AAA'!

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Jul 15 1997 - 00:00:00 CDT