Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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
add comment

2 Answers

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.

share|improve this answer
    
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.stackoverflow.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
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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