SQL Problem

[lavasseur patrick]

Hello

I would like to obtain all the fields of a table where the two fields C1 and
C2 from this table are distinct, what is the request ? I can't find it ...
sorry, and thank you

[Steve Kass]

Re: SQL Problem

Patrick,

What do you mean by "the two fields C1 and C2 from this
table are distinct" ?

If you mean C1 is not equal to C2 (or "C1 is distinct
from C2") then you just need

select * from T
where C1 <> C2

But if you want a result set where the pairs (C1,C2)
are not duplicated in more than one row, your question is
not well-defined. If that is what you want, what would
you want if this is your table?

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

For example, one of the distinct pairs (C1, C2) is (VINET, 5).
If you only want one row for these values, do you want 10 or 11
in column C3? Even if it doesn't matter, you need to be precise
about what you want in order to write the query.

-- Steve Kass
-- Drew University
-- Ref: D1F0FE10-E0DF-4AA4-81BE-ECE3134096E7



lavasseur patrick wrote:[color=blue]
> Hello
>
> I would like to obtain all the fields of a table where the two fields C1 and
> C2 from this table are distinct, what is the request ? I can't find it ...
> sorry, and thank you
>
>[/color]

[lavasseur patrick]

Re: SQL Problem

you're right

I would like
[color=blue]
> 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[/color]

and to obtain
[color=blue]
> C1 C2 C3
> ---------- ---- ----
> VINET 5 10
> TOMSP 5 13[/color]

for the result

[Steve Kass]

Re: SQL Problem

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:[color=blue]
> you're right
>
> I would like
>
>[color=green]
>>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[/color]
>
>
> and to obtain
>
>[color=green]
>>C1 C2 C3
>>---------- ---- ----
>>VINET 5 10
>>TOMSP 5 13[/color]
>
>
> for the result
>
>[/color]