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

;

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

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