Re: SQL question

Paul Chang wrote:
>
> Hi,
>
> Below is the table with duplicated key rows
> key is (quote_id , price_class)
>
> quote_id price_class discount_code discount
> ----------- ----------- ------------- --------------------
> 448 A CATALYST A 30.000000
> 448 A NULL 10.000000
> 448 A CATALYST A 5.000000
> 448 B INT A 40.000000
> 448 B CATALYST A 30.000000
> 448 B INT A 80.000000
>
> How can I use SQL to remove dup lines and just keep the first line in each group?
>
> result:
> 448 A CATALYST A 30.000000
> 448 B INT A 40.000000
Hi Paul,

SQL is a set-based language, so there is not a strict definition of "first line" unless you order by the values in some column. It is true that up to ASE 11.5, rows were returned in a predictable order, but that is no longer always true.

You can limit it to one row per quote_id and price_class combination by selecting the minimum, maximum, or average discount with SQL like

select quote_id, price_class, discount_code, max(discount) from mytable
group by quote_id, price_class

-- 
Bret Halford			Imagine my disappointment
Sybase Technical Support	in learning the true nature		
3665 Discovery Drive		of rec.humor.oracle...
Boulder, CO 80303