Mysql GROUP BY and ORDER BY DESC

Question

This is my full request:

SELECT * FROM `clientgroupassign` LEFT JOIN `clients` ON `clientgroupassign`.clientId = `clients`.clientId LEFT JOIN `users` ON `carerId` = `userId` LEFT JOIN (SELECT * FROM (SELECT * FROM `contacts` WHERE `contactGroup` = 4 ORDER BY `contactId` DESC) as `contacts` GROUP BY (`contactClientId`) ) AS `contacts` ON `contactClientId` = `clients`.clientId WHERE groupId = 4 ORDER BY `clients`.clientId 

There is a problem with the third connection causing the script to execute for about 1 minute. When I run it separately in PMA:

 SELECT * FROM (SELECT * FROM `contacts` WHERE `contactGroup` = 4 ORDER BY `contactId` DESC) AS `contacts` GROUP BY (`contactClientId`) 

still a lot of time to complete.

I want to get one, the last added row from contacts for each client that is in group 4 (the client can be in different groups).

Thanks.

Answer 1

To get the "last row added from contacts for each customer in group 4", try the following:

 SELECT c.* FROM( SELECT contactClientId, MAX(contactId) as cid FROM contacts WHERE contactGroup = 4 GROUP BY contactClientId ORDER BY NULL ) as tmp INNER JOIN contacts as c ON c.contactId = tmp.cid AND c.contactClientId = tmp.contactClientId 

If contactId is PK in contacts , then an offer of a second connection is not required.

By default, MySQL sorts all GROUP BY queries col1, col2, ..., as if you also specified ORDER BY col1, col2, .... if you include the ORDER BY clause explicitly, which contains the same column list, MySQL optimizes it without any speed limits, although sorting is still in progress. If the query includes GROUP BY, but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL.

Full docs .

Full request:

 SELECT * FROM `clientgroupassign` LEFT JOIN `clients` ON `clientgroupassign`.clientId = `clients`.clientId LEFT JOIN `users` ON `carerId` = `userId` LEFT JOIN ( SELECT c.* FROM( SELECT contactClientId, MAX(contactId) as cid FROM contacts WHERE contactGroup = 4 GROUP BY contactClientId ORDER BY NULL ) as tmp INNER JOIN contacts as c ON c.contactId = tmp.cid AND c.contactClientId = tmp.contactClientId ) AS `contacts` ON `contactClientId` = `clients`.clientId WHERE groupId = 4 ORDER BY `clients`.clientId