18

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.

  • 1
    If you had an index(petid,id) on the comments table, the order by would not likely be slow, but first things first: It seems as though your query is asking for all of the pets where userid 'ABC' has commented on them within the last two months, where deviceID isn't 'ABC' (though it's unclear which table deviceID is a column in, possibly pets and possibly comments) and who the last commenter was, and the last comment date. Is that right? – Michael - sqlbot Feb 13 '13 at 23:53
  • @Michael-sqlbot - Yes, that's exactly what I am trying to gather. The deviceID is from pets table - meaning that just do not get me the pets which are submitted by 'ABC' himself. – BufferStack Feb 14 '13 at 6:06
11
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.

  • I had tried this earlier as well... this returns NULL for both lastComDate & lastPosterId for all the records. – BufferStack Feb 13 '13 at 17:56
  • Do you have any sample data available? – Valkyrie Feb 13 '13 at 17:59
  • How do I provide the sample data? – BufferStack Feb 13 '13 at 18:13
  • Try the hints in this post: meta.stackexchange.com/questions/156729/… – Valkyrie Feb 13 '13 at 18:35
  • 1
    That's good, but SQLFiddle is better ;). See an example here. Way better to just see the code and seed data, not some table you'll need to format. – Marian Feb 14 '13 at 8:20
6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.