Gaz Smith Gaz Smith - 1 month ago 6
MySQL Question

Ordering then Grouping in Mysql

I'm trying to order mysql then group it. this is my table :

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|consultantid|int(11)|Yes|NULL
|startdate|datetime|Yes|NULL
|enddate|datetime|Yes|NULL
|target|varchar(255)|No|
|dateinserted|datetime|No|CURRENT_TIMESTAMP
== Dumping data for table consultant_targets

|1|1|2016-11-01 00:00:00|2016-11-29 00:00:00|10000|2016-10-31 21:34:55
|2|1|2016-10-31 00:00:00|2016-11-23 00:00:00|15000|2016-10-31 22:03:09


The second record is inserted later than the first, and thats what i'm looking to get - so if there was 50 of these it would just grab the latest one. This is my SQL :

SELECT
*
FROM
(
SELECT
ct.*,
u.username,
u.colorcode
FROM
consultant_targets ct
INNER JOIN user u ON u.id = ct.consultantid
ORDER BY
dateinserted desc
) AS ctu
GROUP BY
consultantid


But im just getting the first row every time, the one with the 10000 target

Answer

You can use NOT EXISTS() :

SELECT t.*,
       u.username,
       u.colorcode
FROM consultant_targets t
INNER JOIN user u 
 ON u.id = t.consultantid
WHERE NOT EXISTS(SELECT 1 FROM consultant_targets s
                 WHERE t.consultantid = s.consultantid
                   AND s.dateinsereted > t.dateinsereted)