JamesB Posted September 14, 2014 Share Posted September 14, 2014 (edited) I'm having problems doing this in one query. I have these tables: [table: users]user_id user_name1 somename[table: updates]user_id stat_id xp time_seen1 0 100 14106209341 0 120 14106209351 1 50 14106209362 0 80 1410620937 The query should return rows from table `updates` for 1 user, with 1 returned row per unique stat_id, where the xp is the highest xp for that user for that stat. So for user id 1, this should be returned:stat_id xp0 1202 80So for user id 2, this should be returned:stat_id xp0 80 I've tried this: $result = $this->db->query('SELECT updates.stat_id, updates.xp FROM updatesLEFT JOIN users ON users.user_id = updates.user_idWHERE users.user_name = '.$this->db->escape($user).' AND xp == MAX(xp)'); But the MAX(xp) will be the max xp of all stat_id's instead of per stat_id. Edited September 14, 2014 by JamesB Link to comment Share on other sites More sharing options...
JamesB Posted September 14, 2014 Author Share Posted September 14, 2014 It seems to work with: $result = $this->db->query('SELECT updates.stat_id, MAX(xp) as max_xp FROM updates LEFT JOIN users ON users.user_id = updates.user_id WHERE users.user_name = '.$this->db->escape($user).' GROUP BY stat_id'); Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now