Re: SQL Question

This might work...try this...

select colA, colB
from tablename 1
where colC in(select max(colC) from tablename 2   where nvl(2.colA,'X')||nvl(2.colB,'X'=nvl(1.colA,'X')||nvl(1.colB,'X')   group by colA)

Use "nvl"s in the joins in the subquery if you ever will have rows where colA or colB can be null and therefore force equality, as Oracle doesn't consider a null equal to another null. If colA and colB are defined as non-null, then that doesn't apply. Also, the concatenation is optional. You could do this just as easy with another where (and) clause if you wanted to.
--
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com

Lui Yuan Tze <ssplyt_at_pacific.net.sg> wrote in article <6i204o$q42$1_at_newton2.pacific.net.sg>...
> Hi,
> suppose I have the following table:
> table X with
> col A col B col C
> xx y 99
> xx t 100
> yy a 10
> yy d 30
> yy e 5
>
> How do I code SQL to get the values of col A and col B of the
> max col C value for each col A ? ie record no 2 and no 4.
>
> Thanks ahead
Received on Mon Apr 27 1998 - 15:15:09 CDT