+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Getting unqiue records from query (/showthread.php?tid=23840)
Getting unqiue records from query - El Forum - 10-23-2009
[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!
Getting unqiue records from query - El Forum - 10-23-2009
[eluser]rogierb[/eluser]
Take a look at the MAX() or MIN() function e.g. http://www.tizag.com/mysqlTutorial/mysqlmax.php
Getting unqiue records from query - El Forum - 10-23-2009
[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