sqljoinduplicates

SQL Select Max(Date) out of rows with Duplicate Id

I have a table like the below sample. I'm trying to get the row of every user having status 0 and if there is a duplicate UserId then get the row with max(CreatedDate) for that user

TableA

+----+--------+-------------+--------+
| Id | UserId | CreatedDate | Status |
+----+--------+-------------+--------+
|  1 | a234   | 09/02/2017  |      0 |
|  2 | a234   | 09/03/2017  |      0 |
|  3 | a234   | 09/06/2017  |      1 |
|  4 | a143   | 08/25/2017  |      0 |
+----+--------+-------------+--------+

Expected Output

+----+--------+-------------+--------+
| Id | UserId | CreatedDate | Status |
+----+--------+-------------+--------+
|  2 | a234   | 09/03/2017  |      0 |
|  4 | a143   | 08/25/2017  |      0 |
+----+--------+-------------+--------+

SQL Query

SELECT d.Id, d.UserId,d.CreatedDate,d.Status FROM 
(
    SELECT Id, Max(CreatedDate) as MaxDate  
    FROM TableA
    GROUP BY Id
    HAVING Count(*)>1
) r
INNER JOIN TableA d
ON d.Id=r.Id AND d.CreatedDate=r.MaxDate AND Status=0

But I think the condition is wrong which should be like a sequence eg:

1st condition Status=0

2nd Condition If Duplicate UserId exist take Max(CreatedDate) else CreatedDate

Solution

  • Try this:-

    SELECT d.Id, d.UserId,d.CreatedDate,d.Status FROM 
(
    SELECT UserId, Max(CreatedDate) as MaxDate  
    FROM TableA
    WHERE Status = 0
    GROUP BY UserId
) r
INNER JOIN TableA d
ON d.UserId=r.UserId AND d.CreatedDate=r.MaxDate AND Status=0

    Your GROUP BY and JOIN needed to be on UserId for this to work, and I've moved the filter by Status=0 to the derived table / subquery.