CODEWITHSUNDEEP
mysqlsql
Smeaven
Smeaven

Reputation: 147

Get related data from retrieved column with MAX()

SELECT MAX(count) FROM visits

With this example Query I got e.g the most active User of a Page. But with MAX() I can only get the maximal count,e.g max visits of users.

Is it possible, to retrieve the related data (hole column where count came from) to the selected count value, e.g the username?

Upvotes: 0

Views: 51

Answers (2)

Andrew Lazarus
Andrew Lazarus

Reputation: 19330

For anyone who comes here whose RDBMS supports windowing, a clean solution is 

SELECT * FROM
  (SELECT visits.*, rank() AS r
  OVER (ORDER BY visits.count DESC)
  FROM visits) AS subquery
WHERE r=1;

This question is tagged mysql which doesn't have this. Ties for max are handled correctly.

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You could use this query:

SELECT visits.*
FROM   visits
WHERE  count=(SELECT MAX(count) FROM visits)

this query could return more than one row if multiple visits share the same maximum count.

Upvotes: 2

Related Questions