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