CODEWITHSUNDEEP
mysqlsql
GGio
GGio

Reputation: 7643

SELECT Latest Records on Grouped By fields MySQL

SELECT AnotherID, TID, TDate, COUNT(*) AS Total
FROM MyTable
GROUP BY TID
HAVING Total > 1

The above query returns the records where multiple records have same TID. Now I need to select the latest date.

Above query returns TDate but its not the most recent one, basically I need to group by TID and get those records where TDate is most recent. I only need to get those records where multiple records have same TID.

NOTE: Cant use MAX(TDate) because it does not select the proper AnotherID field

Upvotes: 1

Views: 116

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

Assuming TDate is of data type date or datetime, then MAX(TDate) wil give you the latest date for each grouped TID:

SELECT t1.AnotherId, t1.TID, t2.MaxTDate, t2.Total
FROM MyTable AS t1
INNER JOIN
(
   SELECT TID, MAX(TDate) AS MaxTDate, COUNT(*) AS Total
   FROM MyTable 
   GROUP BY TID
   HAVING Total > 1
) AS t2  ON t1.TID   = t2.TID
        AND t1.TDate = t2.MaxTDate;

Upvotes: 2

Related Questions