How do I conditionally select a unique value in SQL?

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'

Answer

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