Home > OS >  How do I conditionally select a unique value in SQL?
How do I conditionally select a unique value in SQL?

Time:05-11

I've been tasked with returning only rows with unique IDs but returning a row for every ID in SQL. How would I go about this?

Logic: For primary row, select where JOB_INDICATOR = ‘P’. If there are multiple rows, then use the record where PRIM_ROLE_IND = ‘Y’. If there are still multiple then select the lowest numbered EMPL_RCD starting at 0.

Example starting point:

id name job job_indicator prim_role_ind empl_rcd
1001 John Doe Director P N 0
1001 John Doe Professor P Y 1
1001 John Doe Coach N N 2
1002 Bob Jones Head Janitor P Y 0
1002 Bob Jones Associate Janitor P Y 1
1003 Susan Smith Groundskeeper P N 0
1003 Susan Smith Professor P N 1

Desired return:

id name job job_indicator prim_role_ind empl_rcd
1001 John Doe Professor P Y 1
1002 Bob Jones Head Janitor P Y 0
1003 Susan Smith Groundskeeper P N 0

So far, I have the below, but a new requirement was added to do conditional components.

SELECT * 
FROM EMPLOYEE 
WHERE JOB_INDICATOR = 'P'

CodePudding user response:

You can use window function ROW_NUMBER() to accomplish this:

SELECT *
FROM 
    (
        SELECT EMPLOYEE.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY 
        prim_role_ind DESC, empl_rcd ASC) as rn
        FROM EMPLOYEE
        WHERE JOB_INDICATOR = 'P'
    ) dt
WHERE rn = 1
  • Related