Getting unqiue records from query

[eluser]hendrik[/eluser]
We're running a campaign featuring a flash game where users can submit their fastest times to the db.

Every time they submit a time, it saves a entry into the 'games' table and references the user by his user_id.

To display the high scores we only want to show each person's best time. Although 'john' might have the top 5 times he is positioned at #1, and jill - who has the 6th fastest time - is positioned at #2, etc.

My current query returns a list of unique users but not their best times. It looks like this:

Code:

$this->db->select('campaign_game.user_id,contacts.screenname,campaign_game.duration');
$this->db->from('campaign_game');
$this->db->where('savedAt >', $startAt);
$this->db->join('contacts', 'contacts.id = campaign_game.user_id');            
$this->db->order_by('campaign_game.duration DESC');
$this->db->limit(10);
$this->db->group_by('campaign_game.user_id');            
$query = $this->db->get();
return $query->result();

I'd appreciate any help on getting unique users and their best times from the db. Thanks!

[eluser]rogierb[/eluser]
Take a look at the MAX() or MIN() function e.g. http://www.tizag.com/mysqlTutorial/mysqlmax.php

[eluser]hendrik[/eluser]
Thanks for putting me in the right direction. This worked:

Code:

SELECT user_id, MIN(duration), screenname FROM campaign_game JOIN dc_contacts ON campaign_game.user_id = contacts.id GROUP BY user_id ORDER BY MIN(duration) ASC