CODEWITHSUNDEEP
sqljoinduplicates
shockwave
shockwave

Reputation: 3272

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

Upvotes: 1

Views: 14061

Answers (4)

Radim Bača
Radim Bača

Reputation: 10701

There is a solution using subquery in WHERE

SELECT d.Id, d.UserId, d.CreatedDate,d.Status 
FROM TableA d
WHERE d.Status  = 0 and d.CreatedDate = 
      (
            SELECT MAX(x.CreatedDate)
            FROM TableA x
            WHERE x.Status  = 0 AND x.UserId = d.UserId
      )

another solution might be using NOT EXISTS

SELECT d.Id, d.UserId, d.CreatedDate,d.Status 
FROM TableA d
WHERE d.Status  = 0 and NOT EXISTS
      (
            SELECT MAX(x.CreatedDate)
            FROM TableA x
            WHERE x.Status  = 0 AND x.UserId = d.UserId AND 
                  x.CreatedDate > d.CreatedDate
      )

or ALL

SELECT d.Id, d.UserId, d.CreatedDate,d.Status 
FROM TableA d
WHERE d.Status  = 0 and d.CreatedDate >= 
      ALL(
            SELECT x.CreatedDate
            FROM TableA x
            WHERE x.Status  = 0 AND x.UserId = d.UserId
      )

Upvotes: 1

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

I believe you will find the below SQL to be more efficient and elegant.

SELECT Id, UserId, CreatedDate, Status,
MAX (CreatedDate) OVER (PARTITION BY USerId) AS max_CreatedDate
FROM TableA
WHERE Status = 0;

Let me know if it works OK for you!

Ted.

Upvotes: 0

Mat Richardson
Mat Richardson

Reputation: 3606

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.

Upvotes: 4

User_Undefined
User_Undefined

Reputation: 27

    select
        ID
        , UserID
        , CreatedDate
        , [Status]
    from TableA A
    inner join (
        select 
            max(CreatedDated) [MaxDate]
            , UserID
            , [Status] 
        from TableA 
        where [Status] = 0 
        group by UserID, [Status]
        ) B on A.UserID = B.UserID and A.CreatedDate = B.MaxDate and A.[Status] = B.[Status]

I think the reason you were getting results that you didn't expect was your "having count(*) > 1" would only return duplicates.

Upvotes: 1

Related Questions