SQL to Find Max date Value from each group- SQL Server

Question

So I have 2 tables which iam joining using Inner Join. Table 1 :

Name	Batch_Date	AcctID
Bob	18-08-11	32
Bob	19-08-11	32
Shawn	18-08-11	42
Shawn	20-08-11	42
Paul	18-08-11	36
Paul	19-08-11	36

Table 2

Code	order_Date	AcctID
1	18-08-11	32
0	NULL	32
0	NULL	42
0	NULL	42
1	18-08-11	36
1	18-08-11	36

So I want to get the name, last batch_date , AcctID from the table 1 and code, order date from table 2.

The challenge for me here is as there are multiple rows of same AcctId in table 2, if for any acctid, the date column is not null, I want to select that date and if date column is null for each row, I want to select the null value for date.

SO resulting dataset should look like below:

Name	Batch_Date	AcctID	Code	Order_Date
Bob	19-08-11	32	1	18-08-11
Shawn	20-08-11	42	0	NULL
Paul	19-08-11	36	1	18-08-11

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Nov 11 '21 at 16:13
@Larnu - Thanks for the quick reply. Top 1 doesnt work because we can get dates that are same and also can get one null and one date for same id. I want if there is 1 null and 1 date- it should pick the date and if there are all nulls in order date then it should pick null. — Jatin Purswani, Nov 11 '21 at 16:18
Why is the desired output showing 18-08-11 for AcctID 42, wouldn't 20-08-11 be the newest? And are you trying to find the newest batch that has an order or just the newest batch and the newest order for each AcctID? — Robert Sheahan, Nov 11 '21 at 16:34
@RobertSheahan - Yes, sorry the date should be 20-08-11. Iam looking to get the all the latest details based on the acct id. — Jatin Purswani, Nov 11 '21 at 16:45
If you use the solutions in that post https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group, you can just do `ORDER BY order_Date DESC` and null rows will get sorted last anyway — Charlieface, Nov 11 '21 at 17:50

Robert Sheahan · Answer 1 · 2021-11-11T17:03:51.953

OK, try this

    --Set up your sample data in useable form, skip in your actual solution
    with cteT1 as (
         SELECT * 
         FROM (VALUES ('Bob', '18-08-11', 32), ('Bob', '19-08-11', 32)
            , ('Shawn', '18-08-11', 42), ('Shawn', '20-08-11', 42)
            , ('Paul', '18-08-11', 36), ('Paul', '19-08-11', 36)
            ) as T1 (CustName, BatchDate, AcctID)
    ), cteT2 as (
         SELECT * 
         FROM (VALUES (1, '18-08-11', 32), (0, NULL, 32), (0, NULL, 42)
            , (0, NULL, 42), (1, '18-08-11', 36), (1, '18-08-11', 36)
            ) as T2 (OrderCode, OrderDate, AcctID)
    ) 
    --Set up the solution - tag the newest of each table
    , cteTopBatches as (
        SELECT ROW_NUMBER() over (PARTITION BY AcctID ORDER BY BatchDate DESC) as BatchNewness, *
        FROM cteT1
    ), cteTopOrders as (
        SELECT ROW_NUMBER() over (PARTITION BY AcctID ORDER BY OrderDate DESC) as OrderNewness, *
        FROM cteT2 --NOTE: NULLs sort below actual dates, but you could use COALESCE to force a specific value to use, probably in another CTE for readability
    )
--Now combine the 2 tables keeping only the newest of each
SELECT T1.AcctID , T1.CustName , T1.BatchDate , T2.OrderCode , T2.OrderDate 
    FROM cteTopBatches as T1 INNER JOIN cteTopOrders as T2 ON T1.AcctID = T2.AcctID 
    WHERE T1.BatchNewness = 1 AND T2.OrderNewness = 1

SQL to Find Max date Value from each group- SQL Server

1 Answers1