cat val1 val2 4 1 1 4 5 3 4 7 2 4 3 4 4 7 1
Now consider the query
SELECT cat, MIN(val1), MAX(val1), MIN(val2), MAX(val2) FROM mytable GROUP BY cat;
I think it should be easy to see that for the group where cat is 4, I'll get the following result:
+-----+-----------+-----------+-----------+-----------+ | cat | MIN(val1) | MAX(val1) | MIN(val2) | MAX(val2) | +-----+-----------+-----------+-----------+-----------+ | 4 | 1 | 7 | 1 | 4 | +-----+-----------+-----------+-----------+-----------+
Which row is that in my table? You see? Even if I only asked for MAX(val1), there are 2 rows with the max value of 7. AS I said before, we get information about each group, but not rows from the table.
Other systems wouldn't even allow your query, because clienthistory_id is neither an aggregate function nor a grouped column. MySQL allows this as a convenience, but you are warned not to use columns whose values are not unique per group, as you will get random (first found, I believe) results. See the manual for more <http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html>.
Fortunately, yours is such a frequently asked question, that the manual has a page describing three solutions. See <http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html>.
Michael
Daevid Vincent wrote:
I have this table:
mysql> select historyvlan_time, historyvlan_vlan, v.clienthistory_id from pe_historyvlan as v join pe_clienthistory using (clienthistory_id) order by historyvlan_vlan, historyvlan_time desc; +------------------+------------------+------------------+ | historyvlan_time | historyvlan_vlan | clienthistory_id | +------------------+------------------+------------------+ | 0503011446 | 4 | 55 | <-- | 0503011440 | 4 | 54 | | 0502181640 | 4 | 29 | | 0502181638 | 4 | 26 | | 0502181508 | 4 | 24 | | 0503021500 | 5 | 73 | <-- | 0503011808 | 6 | 71 | <-- | 0503011452 | 6 | 56 | | 0502181626 | 6 | 25 | | 0502181640 | 7 | 28 | <-- | 0503011805 | 8 | 70 | <-- | 0503011801 | 8 | 68 | | 0503011731 | 8 | 61 | | 0503011730 | 8 | 60 | +------------------+------------------+------------------+ 14 rows in set (0.00 sec)
I am trying to find the id and vlan for the most recent time:
mysql> select max(historyvlan_time), historyvlan_vlan, v.clienthistory_id
from pe_historyvlan as v join pe_clienthistory using (clienthistory_id)
group by historyvlan_vlan order by historyvlan_vlan, historyvlan_time desc;
+-----------------------+------------------+------------------+
| max(historyvlan_time) | historyvlan_vlan | clienthistory_id |
+-----------------------+------------------+------------------+
| 0503011446 | 4 | 24 | <--
| 0503021500 | 5 | 73 |
| 0503011808 | 6 | 25 | <--
| 0502181640 | 7 | 28 | | 0503011805 | 8 | 60 | <--
+-----------------------+------------------+------------------+
5 rows in set (0.00 sec)
Why do I get '24' when I should get '55', '25' when I should get '61', and '60' when I should get '70'.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
