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
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.