CODEWITHSUNDEEP
sqloracle-databaseplsql
ad2387
ad2387

Reputation: 571

Oracle query return one row based on column values

I currently have a query that selects multiple rows of data for the application in the WHERE clause:

SELECT
    APP_NAME,
    NAME,
    ROLE
FROM APP_PERSON
WHERE APP_NAME='ABCD'
ORDER BY APP_NAME,NAME

This returns:

 ABCD    Bob    Executive Director
 ABCD    Kim    Director
 ABCD    Sara   Associate Director
 ABCD    Tim    VP

What I'd like is for this to return one row, with the person that is the lowest titled 'Role'. The order goes: Associate Director, Director, Executive Director, VP (These are the only options).

So for the example above, the only row I'd like returned is:

ABCD   Sara   Associate Director

If there wasn't an Associate Director, I'd want Director. How can I create a query that will do this. I'm assuming some kind of DECODE but wasn't sure where to go with it. Thanks for any help!

Upvotes: 0

Views: 4330

Answers (3)

Dave Sexton
Dave Sexton

Reputation: 11188

How about using a common table expresion like so:

WITH cte AS (
  SELECT
    app_name
    ,name
    ,role
    ,MIN(CASE role
           WHEN 'Associate Director' THEN 1
           WHEN 'Director' THEN 2
           WHEN 'Executive Director' THEN 3 
           WHEN 'VP' THEN 4) AS role_level
  FROM app_person
  WHERE app_name='ABCD'
)
SELECT 
  r.*
FROM cte r
  INNER JOIN (
    SELECT 
      name
      ,MIN(role_level) AS role_level
    FROM cte rl
    GROUP BY name) ON r.name = rl.name AND r.role_level = rl.role_level
ORDER BY 
  r.app_name
  ,r.name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

There is more than one way to approach this question. I prefer to explicitly join in the priorities, then use row_number() to choose the row that you want:

select app_name, name, role
from (SELECT APP_NAME, NAME, ROLE,
             ROW_NUMBER() over (partition by app_name order by priority) as seqnum
      FROM APP_PERSON p join
           (select 'Associate Director' as role, 1 as priority from dual union all
            select 'Director', 2 from dual union all
            select 'Executive Director', 3 from dual union all
            select 'VP', 4 from dual
           ) r
           on p.role = r.role
    ) t
WHERE APP_NAME='ABCD' and seqnum = 1
ORDER BY APP_NAME, NAME

Upvotes: 2

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

The order goes: Associate Director, Director, Executive Director, VP (These are the only options)

I hope the below Query will provide you the Answer.

SELECT
    APP_NAME,
    NAME,
    ROLE
FROM APP_PERSON
WHERE APP_NAME='ABCD'
ORDER BY APP_NAME,NAME
where rownum = 1
order by ROLE

Upvotes: 0

Related Questions