Mysql GROUP BY and ORDER BY DESC [duplicate]

Question

This is my full query:

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 third join causing the script to execute for about 1 minute. When I run it separattly in PMA:

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

it still gets very long to execute.

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

Thanks.

Answer 1

To get "last added row from contacts for each client who is in the group 4" try this:

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 the second join clause is not needed.

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

The complete docs.

Full query:

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