Hello all,

Tbl A contains the following distinct rows:

A|A1|A2|O
A|A1|A2|S
B|B1|B2|O
C|C1|C2|S
D|D1|D2|O
E|E1|E2|S
E|E1|E2|O

The two A rows are identical apart from the col4 values.
The two E rows are identical apart from the col4 values.

Is it possible to select all distinct rows from the table - with the
added twist that if a row is identical to another apart from the col4
value then only select the row with col4 set to 'O'.

In other words rows B, C, D are selected and for A and E rows only the
rows with the col4 value set to 'O' will be selected.

Ta,

Manny

At 6:58 AM -0700 7/25/07, Manny wrote:
>Hello all,
>
>Tbl A contains the following distinct rows:
>
>A|A1|A2|O
>A|A1|A2|S
>B|B1|B2|O
>C|C1|C2|S
>D|D1|D2|O
>E|E1|E2|S
>E|E1|E2|O
>
>The two A rows are identical apart from the col4 values.
>The two E rows are identical apart from the col4 values.
>
>Is it possible to select all distinct rows from the table - with the
>added twist that if a row is identical to another apart from the col4
>value then only select the row with col4 set to 'O'.


select col1, col2, col3, min(col4)
from table
group by col1, col2, col3

will do it.

Or, if you have values in col4 smaller than O for the
doubled-up rows, try this:
select col1, col2, col3,
case when count(*)>1 then 'O' else min(col4) end
from table
group by col1, col2, col3

in this version max would work as well as min, all you want
is a way to get the (one) value for col4 when the other
columns are not duplicated elsewhere.

Karl

Karl,

Thats works a treat.

Thanks,

Manny