Using MAX() to get all columns in the result

Databases
1

I have a campaigns table that records a user’s several responses over time. This query works fine:

select MAX(answerDate) from campaigns where uID = 24411;

This query produces incorrect results:

SELECT uID, MAX(answerDate) AS mostRecent, campaign, answer FROM campaigns
WHERE uID = 24411 and campaign = ‘Gold Upgrade’;

When the query finds MAX(answerDate) how do I go about getting the other columns associated with that row?

Thanks!

2 
SELECT answerDate AS mostRecent
     , answer 
  FROM campaigns
 WHERE uID = 24411 
   AND campaign = 'Gold Upgrade'[COLOR="#0000FF"]
ORDER
    BY answerDate DESC LIMIT 1 [/COLOR]
3

I knew you would offer that suggestion. I was asking for help to try and pinpoint why the following query does not really produce the most recent campaign answer. This is a modification of another one you helped me with a few months ago.

Thanks!

SELECT date_format(c.mostRecent, ‘%m-%d-%Y’) as latestAnswer
, u.uID
, u.firstname
, u.lastname
, u.visited
, c.cID
, date_format(u.registerDate, ‘%m-%d-%Y’) as registered
, c.campaign
, c.answer
FROM users u
INNER
JOIN (SELECT cID, uID, MAX(answerDate) AS mostRecent, campaign, answer FROM campaigns group by uID) AS c
ON c.uID = u.uID
where (u.subscription = ‘Free’ or u.subscription = ‘Silver’)
order by c.mostRecent desc;

4

i’m a little confused

what query do you want help with?

5

The query right above…the one with the join. For some reason it is finding the correct date of the most recent answer, but not the correct associated answer. The campaigns table has the following columns:

cID
campaign
answerDate
uID
answer

Thanks!

6

MAX() is an aggregate function in which case you will get one row back which is arbitrary in this case. In order to connect the other row would require a join on the answerDate and uID. However, even that will not guarantee the correct result if more than one row exists per uID with the same answerDate. Though, I doubt that is the case here.

7

not tested but it looks about right:


SELECT 
      date_format(c.answerDate, '%m-%d-%Y') as latestAnswer
     ,u.uID
     ,u.firstname
     ,u.lastname
     ,u.visited
     ,c.cID
     ,date_format(u.registerDate, '%m-%d-%Y') as registered
    ,c.campaign
    ,c.answer
FROM 
    users u
INNER
JOIN 
   (SELECT 
          uID
         ,MAX(answerDate) mostRecent 
      FROM 
         campaigns
     GROUP
        BY
         uID) r
   ON
     u.uID = r.uID
INNER
 JOIN
     campaigns c
   ON
     r.uID = c.uID
  AND
     r.mostRecent = c.answerDate
WHERE
    u.subscription IN ('Free', 'Silver')
ORDER
   BY
    c.answerDate desc;
  • I miss the SQL tags
8

That did the trick. Thanks!