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)ENDFROM ...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