Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-30-2014
11:21 PM

11-30-2014
11:21 PM

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

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

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-30-2014
11:45 PM

11-30-2014
11:45 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-01-2014
12:40 AM

12-01-2014
12:40 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-01-2014
01:39 AM

12-01-2014
01:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-01-2014
03:06 AM

12-01-2014
03:06 AM

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

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