mysql - min max - group_by problem please help me guys - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21) +--- Thread: mysql - min max - group_by problem please help me guys (/showthread.php?tid=31125) |
mysql - min max - group_by problem please help me guys - El Forum - 06-07-2010 [eluser]Richard Schumann[/eluser] ok guys, lets say you have a mysql table what contains diferent user_ids, a value and a timestamp . TABLE: id(int), user_id(int), value(decimal 6,2), timestamp(varchar) with about 100.000 rows like this : 1, 1, 100, 1275866482 2, 1, 110, 1275866472 3, 1, 111, 1275866071 1, 2, 150, 1275866482 2, 2, 210, 1275866472 3, 2, 141, 1275866071 now you wanna know the MIN(value) for each user_id (for 1 and 2 in this example) AND the RELATED timestamp for the ROW what IS the MIN(value) Code: $result = $this looks pretty nice to me to do that, the MAX(value) is total ok , but the timestamp is not the timestamp of the row what have the MAX(value), its another one - and i cant find out how to solve that - was trying it today about 7 hours now ... mysql - min max - group_by problem please help me guys - El Forum - 06-07-2010 [eluser]danmontgomery[/eluser] Code: $result = $this->db->order_by('value','DESC')->limit(1)->get('table')->result_array(); mysql - min max - group_by problem please help me guys - El Forum - 06-07-2010 [eluser]Richard Schumann[/eluser] nope :-) i want not just a single row result ... it had a reason why to use min and group and all that stuff. i wish its so easy :-) but i need for EVERY user the MAX value and the timestamp and the id. i figured it out right now. Since the first query is grouped, it needs to be put into a subquery so it can be joined against the non-grouped table. Code: $max = $this->db->select('f.user_id, f.timestamp, f.value') mysql - min max - group_by problem please help me guys - El Forum - 06-07-2010 [eluser]Richard Schumann[/eluser] like that Code: +--------+------------+-------+ mysql - min max - group_by problem please help me guys - El Forum - 06-17-2010 [eluser]gungbao[/eluser] This will do the Job. Here my Test mySQL-Scheme: Code: CREATE TABLE IF NOT EXISTS `aatest` ( ..and the query: Code: SELECT o.id,o.user_id,o.value,o.times FROM aatest AS o I leave it up to you to make schema and query more eye-candy :-) |