365 Days of SQL: Day 147

There’s been some construction happening in the floor above and its making our lives very uncomfortable. One of the cons of living in a condo. It’s going to be like this until 31st Dec. Why did they have to select such timing? Ahh geezz!

Puzzle #32: First and Last

I tried the solution in the live oracle environment here.

WITH temp AS (
SELECT 1001 AS SpacemanID, 'Astrogator' AS JobDescription, 6 AS MissionCount FROM DUAL UNION ALL
SELECT 2002 AS SpacemanID, 'Astrogator' AS JobDescription, 12 AS MissionCount FROM DUAL UNION ALL
SELECT 3003 AS SpacemanID, 'Astrogator' AS JobDescription, 17 AS MissionCount FROM DUAL UNION ALL
SELECT 4004 AS SpacemanID, 'Geologist' AS JobDescription, 21 AS MissionCount FROM DUAL UNION ALL
SELECT 5005 AS SpacemanID, 'Geologist' AS JobDescription, 9 AS MissionCount FROM DUAL UNION ALL
SELECT 6006 AS SpacemanID, 'Geologist' AS JobDescription, 8 AS MissionCount FROM DUAL UNION ALL
SELECT 7007 AS SpacemanID, 'Technician' AS JobDescription, 13 AS MissionCount FROM DUAL UNION ALL
SELECT 8008 AS SpacemanID, 'Technician' AS JobDescription, 2 AS MissionCount FROM DUAL UNION ALL
SELECT 9009 AS SpacemanID, 'Technician' AS JobDescription, 7 AS MissionCount FROM DUAL
), temp2 AS (
SELECT SpacemanID, JobDescription, MissionCount, ROW_NUMBER() OVER (PARTITION BY JobDescription ORDER BY MissionCount DESC) AS rnk
FROM temp
), 
temp5 AS (
SELECT SpacemanID, JobDescription, MissionCount, ROW_NUMBER() OVER (PARTITION BY JobDescription ORDER BY MissionCount ASC) AS rnk
FROM temp
), 
temp3 AS (
SELECT JobDescription, SpacemanID AS MostExperienced 
FROM temp2
WHERE rnk = 1
ORDER BY JobDescription),
temp4 AS (
SELECT JobDescription, SpacemanID AS LeastExperienced 
FROM temp5
WHERE rnk = 1
ORDER BY JobDescription)

select a.JobDescription, a.MostExperienced , b.LeastExperienced 
FROM temp3 a 
INNER JOIN temp4 b
ON a.JobDescription = b.JobDescription;