MySQL DISTINCT/GROUP BY with MAX()

Question

PROBLEM: I'm trying to pull a list of all records, DISTINCT by the Name using the highest Added timestamp number for that name. I use all results columns in a PHP fetch.

Have a simple database with records as follow:

---ID-------NAME-------VERSION-----------ADDED-------
|  500   |  TheSame   |    1    |  UNIX_TIMESTAMP() |
|  501   |  TheSame   |    2    |  UNIX_TIMESTAMP() |           
|  502   |  SameName  |    30   |  UNIX_TIMESTAMP() |
|  503   |  SameName  |    31   |  UNIX_TIMESTAMP() |
-----------------------------------------------------

ID is auto-increment. Tried a ton of different things and can get the DISTINCT or GROUP BY to work fine, but I end up with the lowest record (ex: SameName record #502 and version #30, when it should be #503 and version #31.

No JOIN or anything going on here, so this should be pretty simple.

[This](https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html) seems a similar problem and they _do_ use JOIN. But to be honest, I also think there must be a possibility to avoid it. — PerlDuck, Mar 09 '16 at 19:40

Asif Mehmood · Accepted Answer · 2016-03-09T19:52:19.647

1

Give this try(Possible way to get the latest-version number:

SELECT NAME, COUNT(*) AS VERSION
FROM TABLENAME
GROUP BY NAME

If above, does not meet the requirement, try this:

SELECT * FROM 
(
    SELECT 
        NAME, ID, VERSION, ADDED
    FROM 
        TABLENAME
    ORDER BY 
        NAME, VERSION DESC, ID, ADDED 
) myAlias
GROUP BY NAME

edited Mar 09 '16 at 19:52

answered Mar 09 '16 at 19:15

Asif Mehmood

964
2
14
35

Updated the answer.... @user3223880 – Asif Mehmood Mar 09 '16 at 19:31
1

The latter gives the max. version but a random (?) timestamp -- not the corresponding one. – PerlDuck Mar 09 '16 at 19:43
It will work fine now, thanks to point me out @PerlDog – Asif Mehmood Mar 09 '16 at 19:53

MySQL DISTINCT/GROUP BY with MAX()

1 Answers1