SELECTing multiple columns through a subquery

Question

I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.

SELECT
  DISTINCT petid,
  userid,
  (SELECT MAX(comDate) FROM comments WHERE petid=pet.id) AS lastComDate,
  (SELECT userid FROM comments WHERE petid=pet.id ORDER BY id DESC LIMIT 1) AS lastPosterID
FROM 
  pet LEFT JOIN comments ON pet.id = comments.petid
WHERE 
  userid='ABC'      AND 
  deviceID!='ABC'   AND 
  comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH);

Basically, I am trying to get the lastComDate & lastPosterID from the same row - the row which is the latest one in comments for the specific pet. Please suggest how can I get them in an efficient way.

The above query works, but seems overkill as same row is fetched twice. Moreover, the ORDER BY clause is significantly slower than the aggregate function - as I found while profiling query. So, a solution avoiding sorting would be appreciated.

Answer 1

SELECT DISTINCT petid, userid, lastComDate, lastPosterId
FROM 
    pet 
    LEFT JOIN comments ON pet.id = comments.petid 
    LEFT JOIN (
        SELECT MAX(comDate), userid, petid FROM comments GROUP BY userid
    ) a ON a.petid = pet.id
WHERE 
    userid='ABC' 
    AND deviceID!='ABC' 
    AND comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH)
;

You can also pull your subquery out into a temp table if performance becomes impacted somewhere down the road.

Answer 2

Given that your tables look like this :

create table pet (id int, userid int, deviceid int);
create table comments (id int, petid int, comdate date);

This query should do the trick :

SELECT 
        p.id, 
        p.userid,
        (SELECT MAX(comDate)
         FROM comments
         WHERE petid = p.id
         AND comDate >= DATE_SUB(
                 CURRENT_TIMESTAMP, INTERVAL 2 MONTH)
               ) AS lastComDate,
        (SELECT userid
         FROM comments
         WHERE petid = p.id
         AND comDate >= DATE_SUB(
              CURRENT_TIMESTAMP, INTERVAL 2 MONTH
         ) ORDER BY id DESC LIMIT 1) AS lastPosterID
    FROM 
        pet p

    WHERE 
        p.userid=1
        AND p.deviceID!=1