I have a table with multiple columns, but I will make it easy and just use three.
CREATE TABLE TEMPTBL(
empl_id varchar(8),
empl_no varchar(6),
sep_dt date
);
INSERT INTO TEMPTBL
VALUES
('IS000001', '112233', NULL),
('00418910', '112233', '1/1/2019');
What I am trying to get is the row where either the sep_dt is null or the row where that has the max sep_dt grouped by the empl_no field. So in the case of my example, it would be that first row, 'IS000001' (I just want the select to return the empl_id). Say the Null was '6/8/2018' instead, then I would want to return '00418910'.
I have tried, but I know it is wrong because you can't use max like that:
SELECT empl_id
FROM TEMPTBL empl
WHERE empl_no = '112233' AND (
sep_dt IS NULL OR
MAX(sep_dt)
)
I know it must include a group by or maybe a join. So I came up with this:
SELECT empl_id
FROM TEMPTBL
INNER JOIN (
SELECT empl_no, max(sep_dt) as sep_dt
FROM TEMPTBL
WHERE empl_no = '112233'
GROUP BY empl_no
) emp ON TEMPTBL.empl_no = emp.empl_no AND TEMPTBL.sep_dt = emp.sep_dt
This will give me the row with the date in it, not the one with the null value. So it will work if all the sep_dt fields have values, but if one in NULL, it doesnt work, because i want the row with the null value. What am I miss?
I think you can simply use ROW_NUMBER
with a special ORDER BY
clause:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY empl_no
ORDER BY CASE WHEN sep_dt IS NULL THEN 1 ELSE 2 END, sep_dt DESC
) AS rn
FROM t
) AS x
WHERE rn = 1