06-07-2010, 11:16 AM
[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)
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 ...
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
->db->from('table')
->select('MAX(value) AS max, user_id, timestamp')
->group_by('user_id')
->get()->result_array();
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 ...