sqlsql-servernullgreatest-n-per-group

How to select the row where the date is null or max date for that group


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?


Solution

  • 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
    

    Demo on db<>fiddle