Help with a MySQL query, selecting a maximum element

Question

I have a table with 4 columns: place_id, username, counter, last_checkin

I'm writing a check-in based system and I'm trying to get a query that will give me the "mayor" of each place. The mayor is the one with most check-ins, and if there is more than 1 than the the minimum last_checkin wins.

For example, if I have:

place_id, username, counter, last_checkin
 123,     tom,       3 ,      13/4/10
 123,     jill,      3,       14/4/10
 365,     bob,       2,       15/4/10
 365,     alice,     1,       13/4/10

I want the result to be:

123, tom
365, bob

I'm using it in PHP code

---

Here is the test data:

CREATE TABLE `my_table` ( `place_id` int(11), `username` varchar(50), `counter` int(11), `last_checkin` date);
INSERT INTO `my_table` VALUES (123,'tom',3,'2010-04-13'),(123,'jill',3,'2010-04-14'),(365,'bob',2,'2010-04-15'),(365,'alice',1,'2010-04-13');

Answer 1

$data = query("SELECT max(counter) counter,username FROM table GROUP By place_id ORDER By last_checkin DESC");

Answer 2

How about..

SELECT
    place_id,
    (SELECT username
         FROM my_table MT2
         WHERE MT2.place_id = MT1.place_id
         ORDER BY counter DESC, last_checkin ASC
         LIMIT 1) AS mayor
    FROM my_table MT1
    GROUP BY place_id;

Edited as Unreason suggests to have ascending order for last_checkin.

Answer 3

Brian's correlated query is something I would write. However I found this different take and it might perform differently depending on the data

SELECT
    mt1.place_id,
    mt1.username
FROM 
    my_table mt1 LEFT JOIN my_table mt2 
        ON mt1.place_id = mt2.place_id AND
           (mt1.counter < mt2.counter OR 
            (mt1.counter = mt2.counter AND mt1.last_checkin > mt2.last_checkin)
           )
WHERE 
    mt2.place_id IS NULL

Which uses left join to get to the top records according to certain conditions.