Last 3 Employment information for each person in a ROW

Question

I need to display the last three employer names for each person in a row.

I have used the Rank() and Max() approach and was wondering if there is an easy way to solve this using analytics functions. I tried using NTH_VALUE but I still needed the max function to display the results in a row format.

SELECT 
ID,
MAX(CASE WHEN RANK=1 THEN EMPLOYER_NAME END) EMPLOYER_NAME1,
MAX(CASE WHEN RANK=2 THEN EMPLOYER_NAME END) EMPLOYER_NAME2,
MAX(CASE WHEN RANK=3 THEN EMPLOYER_NAME END) EMPLOYER_NAME3

FROM (
SELECT ID,EMPLOYER_NAME,RANK() OVER (PARTITION BY ID ORDER BY START_DATE DESC) RANK
FROM EMP
)

WHERE RANK<4
GROUP BY ID

Desired output:

ID,  EMPLOYER_NAME1, EMPLOYER_NAME2, EMPLOYER_NAME3
max, delotte       , apple,          google

score 1 · Answer 1 · answered Jul 19 '19 at 14:24

You need a GROUP BY:

SELECT ID,
       MAX(CASE WHEN RANK = 1 THEN EMPLOYER_NAME END) as EMPLOYER_NAME1,
       MAX(CASE WHEN RANK = 2 THEN EMPLOYER_NAME END) as EMPLOYER_NAME2,
       MAX(CASE WHEN RANK = 3 THEN EMPLOYER_NAME END) as EMPLOYER_NAME3    
FROM (SELECT ID,EMPLOYER_NAME,RANK() OVER (PARTITION BY ID ORDER BY START_DATE DESC) RANK
      FROM EMP
    ) e
WHERE RANK < 4
GROUP BY id;

Note that ELSE NULL is unnecessary because that is how CASE expressions work without an ELSE anyway.

Last 3 Employment information for each person in a ROW

1 Answers1