CODEWITHSUNDEEP
sqlmax
Señor Reginold Francis
Señor Reginold Francis

Reputation: 16936

SQL MAX() question

uid     timestamp
1   1242420497
1   1243534661
1   1243534858
1   1243611312
1   1243611511
3   1244817764
3   1244819093
1   1244749446

I have this table, and I am lookng to grab the row that has the highest time stamp. I tried using 

SELECT uid,max(timestamp) FROM `node_revisions` WHERE nid=51

but that returned

uid timestamp
1   1244819093

which has the wrong uid as you can see. How would I make it grab the uid from the correct row? thanks

Upvotes: 0

Views: 9324

Answers (6)

Eric
Eric

Reputation: 95203

You're missing the GROUP BY clause.

SELECT
    uid,
    max(timestamp) as max_time
FROM
    node_revisions
WHERE
    nid = 51
GROUP BY
    uid
ORDER BY 
    max_time DESC 
LIMIT 1

Upvotes: 5

Carl Manaster
Carl Manaster

Reputation: 40356

SELECT uid, timestamp 
FROM node_revisions 
WHERE timestamp = (SELECT MAX(timestamp) FROM node_revisions);

Updated per Ryan Oberoi's comment; since we're getting just the one record, MAX() in the outer query is unnecessary. Thanks.

Upvotes: -1

Eric
Eric

Reputation: 8088

This will work just fine..im sure

select uid FROM `node_revisions`
WHERE uid=51 and timestamp = (select max(timestamp) where uid = 51)
group by uid

Upvotes: 1

Ryan Oberoi
Ryan Oberoi

Reputation: 14505

SELECT * FROM node_revisions WHERE nid=51 ORDER BY timestamp DESC LIMIT 1

Upvotes: 5

Justin Balvanz
Justin Balvanz

Reputation: 1186

Your example uses nid=51 instead of uid=51. Is this code copied directly from what you're running? If there is a nid field, this may be your issue. And you need a group by clause.

SELECT uid, max(timestamp) as max_time
FROM 'node_revisions'
WHERE uid = 51
GROUP BY uid

Upvotes: 2

Jonathan Fingland
Jonathan Fingland

Reputation: 57197

first thing, nid does not appear in your table.

second, I suspect you want group by uid

Upvotes: 2

Related Questions