Patrick,
In this particular case, you could write
select C1, C2, min(C3) as C3
from T
group by C1, C2
but if you have more than three columns or
don't want the minimum value of the third
column, you need a clear definition of what
values you want past the first two columns.
Do you want any value? Must the remaining column
values all come from the same row? Do you want the
one with the smallest or largest value of some
specific column?
Generally queries like this might look like
select C1, C2, C3, C4, C5
from T T1
where not exists (
select * from T T2
where T2.C1 = T1.C1
and T2.C2 = T1.C2
and (
T2.C3 < T1.C3 or (
T2.C3 = T1.C3 and T2.C4 < T1.C4) or (
T2.C3 = T1.C3 and T2.C4 = T1.C4 and T2.C5 < T1.C5)
)
)
SK
lavasseur patrick wrote:
you're right
I would like
C1 C2 C3
---------- ---- ----
VINET 5 10
VINET 5 11
VINET 5 12
TOMSP 5 13
TOMSP 5 13
TOMSP 6 18
TOMSP 7 19
TOMSP 6 19
TOMSP 7 18
and to obtain
C1 C2 C3
---------- ---- ----
VINET 5 10
TOMSP 5 13
for the result