selecting one record out of many records depending on few rules

Database
Enthusiast

selecting one record out of many records depending on few rules

Hi , I tried checking forums before posting this query. Please suggest 

I am  getting below o/p by joining couple of tables

ACT ACTIND

A1   01

A1   02

A1   02

A1   03

A2   01

A2   03

A2   03

A3   01

A3   01

A3   01


Actually I wanted below o/p by using above result.

ACT ACTIND

A1 02

A2 03 

A3 01


( If a act has more than one combination of Indicator select only one from them using some rules 

For ex :  if a particualr  act has 01,02,03 then only select 02 

          if a act has 01 , 03 then select only 03 



Tags (2)
4 REPLIES
Junior Contributor

Re: selecting one record out of many records depending on few rules

Is it possible to define the rules using an ORDER BY? 

QUALIFY 
ROW_NUMBER()
OVER (ORDER BY
CASE ACTIND
WHEN 3 THEN -2
WHEN 2 THEN -1
ELSE ACTIND
END) = 1

Otherwise you might do some CASE/MAX, how many distinct ACTINDs and rules actually exist?

Enthusiast

Re: selecting one record out of many records depending on few rules

Thanks Dieter for the response . 

There are 4 rules 

If 01 02 and 07 then ind  to 02 

if 01 and 02 then ind  to 02 

if 01 and 07 then ind  to 07 

if 02 and 07 then ind  to 02

I will try to run your logic and see 

------

Thanks 

Junior Contributor

Re: selecting one record out of many records depending on few rules

SELECT ACT,
CASE
WHEN
MIN(CASE WHEN ACTIND = '01' THEN 1 END) +
MIN(CASE WHEN ACTIND = '02' THEN 1 END) +
MIN(CASE WHEN ACTIND = '07' THEN 1 END) IS NOT NULL
THEN '02'
WHEN
MIN(CASE WHEN ACTIND = '01' THEN 1 END) +
MIN(CASE WHEN ACTIND = '02' THEN 1 END) IS NOT NULL
THEN '02'
WHEN
MIN(CASE WHEN ACTIND = '01' THEN 1 END) +
MIN(CASE WHEN ACTIND = '07' THEN 1 END) IS NOT NULL
THEN '07'
WHEN
MIN(CASE WHEN ACTIND = '02' THEN 1 END) +
MIN(CASE WHEN ACTIND = '07' THEN 1 END) IS NOT NULL
THEN '02'
ELSE MAX(ACTIND)
END
FROM ...
GROUP BY 1

Or a simplified CASE

CASE 
WHEN
MIN(CASE WHEN ACTIND IN ('01','07') THEN 1 END) +
MIN(CASE WHEN ACTIND = '02' THEN 1 END) IS NOT NULL
THEN '02'
WHEN
MIN(CASE WHEN ACTIND = '01' THEN 1 END) +
MIN(CASE WHEN ACTIND = '07' THEN 1 END) IS NOT NULL
THEN '07'
ELSE MAX(ACTIND)
END
Enthusiast

Re: selecting one record out of many records depending on few rules

Thanks much Dieter , I understood the logic and its working fine