All Forums Database
02 Apr 2012
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

Regards Chanchal Preet Singh
Tags:
Bryce 3 posts Joined 11/09
02 Apr 2012

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
;

ulrich 816 posts Joined 09/09
03 Apr 2012

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;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

03 Apr 2012

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

Regards
Chanchal Preet Singh

dnoeth 4628 posts Joined 11/04
03 Apr 2012

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

Dieter

You must sign in to leave a comment.