List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:March 17 2005 4:37am
Subject:Erroneus column using MAX() and GROUP BY
View as plain text  
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'.

Thread
Erroneus column using MAX() and GROUP BYDaevid Vincent17 Mar
  • Re: Erroneus column using MAX() and GROUP BYMichael Stassen17 Mar
    • RE: Erroneus column using MAX() and GROUP BYElton Clark18 Mar