[El Forum]

[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 ...

[El Forum]

[eluser]danmontgomery[/eluser]

Code:

$result = $this->db->order_by('value','DESC')->limit(1)->get('table')->result_array();

[El Forum]

[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();

[El Forum]

[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 |
+--------+----------+-------+

[El Forum]

[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 :-)