[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
->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 ...
[eluser]danmontgomery[/eluser]
Code: $result = $this->db->order_by('value','DESC')->limit(1)->get('table')->result_array();
[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')
->from(' ( select user_id, max(value) as maxvalue from weight group by user_id )
as x inner join weight as f on f.user_id = x.user_id and f.value = x.maxvalue ')
->get()->result_array();
[eluser]Richard Schumann[/eluser]
like that
Code: +--------+------------+-------+
| type | variety | price |
+--------+------------+-------+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| apple | limbertwig | 2.87 |
| orange | valencia | 3.59 |
| orange | navel | 9.36 |
| pear | bradford | 6.05 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
| cherry | chelan | 6.33 |
+--------+------------+-------+
select f.type, f.variety, f.price
from (
select type, min(price) as minprice
from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;
+--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| cherry | bing | 2.55 |
| orange | valencia | 3.59 |
| pear | bartlett | 2.14 |
+--------+----------+-------+
[eluser]gungbao[/eluser]
This will do the Job.
Here my Test mySQL-Scheme:
Code: CREATE TABLE IF NOT EXISTS `aatest` (
`id` int(10) NOT NULL auto_increment,
`user_id` int(10) default NULL,
`value` int(10) default NULL,
`times` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
INSERT INTO `aatest` (`id`, `user_id`, `value`, `times`) VALUES (1, 1, 100, '2010-06-17 23:21:58'), (2, 1, 101, '2010-06-17 23:17:50'), (3, 1, 102, '2010-06-17 23:22:35'), (4, 2, 100, '2010-06-17 23:22:53');
..and the query:
Code: SELECT o.id,o.user_id,o.value,o.times FROM aatest AS o
INNER JOIN (SELECT user_id, MAX(value) AS tim FROM aatest GROUP BY user_id) AS j
ON (j.user_id=o.user_id AND o.value=j.tim)
I leave it up to you to make schema and query more eye-candy :-)
|
