All Forums Database
janthony 9 posts Joined 02/10
30 Nov 2014
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 

 

 

dnoeth 4628 posts Joined 11/04
30 Nov 2014

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?
 

Dieter

janthony 9 posts Joined 02/10
01 Dec 2014

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 

 

dnoeth 4628 posts Joined 11/04
01 Dec 2014
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

 

Dieter

janthony 9 posts Joined 02/10
01 Dec 2014

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

You must sign in to leave a comment.