Re: A newbie question about select record with distinct colum?

Falko Rotter wrote:
>
> Hi,
>
> > I need a help to learn how to select a distinct row from a table which
> > some of the columns are duplicated. For example:
> > Table I has
> > c1 c2 c3 c4
> > row1 A x y z
> > row2 A g e f
> > row3 B l m n
> >
> > and I only want to get row1 ( or row2) and row3. Greatly appreciate
> > your tip.
>
> i think that only column c1 is the field which should not be duplicated.
> Please correct me if i have understood wrong.
>
> try this:
>
> select *
> from table1
> where rowid in (select min(rowid) as r from table1 group by c1);
>
> Regards,
> Falko Rotter
>
> _________________________________________
>
> - Software Developer -
>
> Rotter & Kalweit Softwaredesign GbR
> Friemarer Straße 38
>
> 99867 Gotha
> GERMANY

Sorry for my example to mislead you. Your tip is very useful. Let me give you a better example:

   c1 c2 c3 c4 c5

A1-X21   000123456  100  EA   part_A
A1-X22   000123456  100  EA   part_A
B1-Z12   000123456    2  EA   part_A
C1-X13   000123456   10  EA   part_A
B1-Z11   000654321   20  EA   part_B

and I want my select result as
A1-X21 000123456 100 EA part_A
B1-Z11 000654321 20 EA part_B

My proposed query is as
select c1, x.c2, x.c3, c4, c5
from tableA x, ( select c2, MAX(c3) c3 from tableA group by c2) y where x.c2 = y.c2
and x.c3 = y.c3
but this only give me

A1-X21   000123456  100  EA   part_A
A1-X22   000123456  100  EA   part_A
B1-Z11   000654321   20  EA   part_B

if I replace the c3 with c1, I got
C1-X13 000123456 10 EA part_A
B1-Z11 000654321 20 EA part_B

if I mix both c1 and c3
I got
C1-X13 000123456 100 EA part_A
B1-Z11 000654321 20 EA part_B

Thanks Received on Fri Aug 17 2001 - 23:47:54 CDT