Sql query help

Database
Enthusiast

Sql query help


My input data is as follows:






Product category value
1 Entry 2400
1 Secondry 1200
1 Premium 2500
1 Entry 1200
2 Secondry 1200
2 Premium 2500
3 Secondry 1200
3 01:10 250
4 Entry+ 240
4 Entry 500
5 Secondry 200

Following are the business logics i have to implement on the input data

1) If there is only one product then take that record. ( e.g. Product 5)

2) If there are more than one product then take the row  which has max(value) and Category is Entry. 

3) If there are more than one product and category "Entry" is not available  then check for the category premium and max(value).

4) If both categories Entry and premium does not exist then ignore them.

Expected output after applying the business logic is as follows:






Expected output    
Joining column category value
1 Entry 2400
2 Premium 2500
4 Entry 500
5 Secondry 200

Can you please suggest the query for it. I tried qualify rank function but could not figure how to implement the logic of step 2 and 3 mentioned above

Tags (1)
4 REPLIES
Fan

Re: Sql query help

Hello there

I was able to produce your requirement with the below

Hope it assists, Bryce

 

create volatile table bsw_0

(

  product integer

, category varchar(20)

, xvalue integer

) on commit preserve rows;

insert into bsw_0 values (1,'Entry',2400);

insert into bsw_0 values (1,'Secondry',1200);

insert into bsw_0 values (1,'Premium',2500);

insert into bsw_0 values (1,'Entry',1200);

insert into bsw_0 values (2,'Secondry',1200);

insert into bsw_0 values (2,'Premium',2500);

insert into bsw_0 values (3,'Secondry',1200);

insert into bsw_0 values (3,'01:10',250);

insert into bsw_0 values (4,'Entry+',240);

insert into bsw_0 values (4,'Entry',500);

insert into bsw_0 values (5,'Secondry',200);

--- The bit you care about

sel

  product

, category

, xvalue

from

  bsw_0

where

  product in

  (sel product from bsw_0 having sum(1)=1 group by 1)

UNION

sel

  product

, category

, xvalue

from

(

sel

  product

, category

, xvalue

, case

    when category='Entry' then 0

    else 1

  end rankval

, row_number () over

  (partition by product order by

    rankval

  , xvalue desc

  ) as row_num

from

  bsw_0

) a

where

  product in

  (sel product from bsw_0 where category in ('Entry','Premium')) and

  row_num=1

;

Supporter

Re: Sql query help

this migh do without union 

select product,
substr(val,16) as category,
cast(substr(val,5,11) as integer) as xvalue
from
(
select product, max(case when category = 'Entry' then 2
when category = 'Premium' then 1
else 0
end !! xvalue !! category
) as val, count(*) as cnt
from bsw_0
group by 1
) as t
where cnt = 1
or (cnt > 1 and category in ('Entry','Premium'))
order by 1;

Enthusiast

Re: Sql query help

@Bryce and ulrich, thanks a lot for the help.

Senior Apprentice

Re: Sql query help

You can also move the logic into QUALIFY:

SELECT * FROM bsw_0
QUALIFY COUNT(*) OVER (PARTITION BY product) = 1
OR
(category IN ('Entry', 'Premium')
AND ROW_NUMBER()
OVER (PARTITION BY product
ORDER BY CASE category
WHEN 'Entry' THEN 1
WHEN 'Premium' THEN 2
ELSE 3
END, xvalue DESC) = 1
)

Dieter