Data returned not from the same row
-
I am accessing a database using the following command:
SELECT SensorID, State, NumKey, MAX(DateTimeEvent) FROM Sensors GROUP BY SensorIDThe SensorID and DateTimeEvent match but State and NumKey are not from the same row as SensorID and DateTimeEvent. The State and NumKey are coming from the first entry found in the table. So the command returns the following: SensorID - ABC123 State - 0 NumKey - 16 DateTimeEvent - 2009-11-17 13:00:00 But the State and NumKey, associated with the SensorID ABC123 and DateTimeEvent 2009-11-17 13:00:00 are 4 and 46 so looking at the row in the table it is: SensorID - ABC123 State - 4 NumKey - 46 DateTimeEvent - 2009-11-17 13:00:00 Any idea what is going on? What I am trying to do is sort on SensorID and pick the row with the highest DateTimeEvent. Thanks, JimI told my daughter age 7 to hold her breath, it was OK as I would be breathing. She looked at me oddly but complied. When she could no longer hold her breath she blew it out gasping. I asked why she let out her breath. She yelled angrily, "DADDY, YOU CAN'T BREATHE FOR ME. I HAD TO BREATHE OR I WOULD DIE!" I smiled and quietly told her, "Just as breathing is essential to life and can be done only by you for you, so is thinking. Do not ever believe that you can let someone else do your thinking."
-
I am accessing a database using the following command:
SELECT SensorID, State, NumKey, MAX(DateTimeEvent) FROM Sensors GROUP BY SensorIDThe SensorID and DateTimeEvent match but State and NumKey are not from the same row as SensorID and DateTimeEvent. The State and NumKey are coming from the first entry found in the table. So the command returns the following: SensorID - ABC123 State - 0 NumKey - 16 DateTimeEvent - 2009-11-17 13:00:00 But the State and NumKey, associated with the SensorID ABC123 and DateTimeEvent 2009-11-17 13:00:00 are 4 and 46 so looking at the row in the table it is: SensorID - ABC123 State - 4 NumKey - 46 DateTimeEvent - 2009-11-17 13:00:00 Any idea what is going on? What I am trying to do is sort on SensorID and pick the row with the highest DateTimeEvent. Thanks, JimI told my daughter age 7 to hold her breath, it was OK as I would be breathing. She looked at me oddly but complied. When she could no longer hold her breath she blew it out gasping. I asked why she let out her breath. She yelled angrily, "DADDY, YOU CAN'T BREATHE FOR ME. I HAD TO BREATHE OR I WOULD DIE!" I smiled and quietly told her, "Just as breathing is essential to life and can be done only by you for you, so is thinking. Do not ever believe that you can let someone else do your thinking."
This is a strange phenomenon in MySQL; Oracle and SQL Server won't even run this query. In a Select statement with a Group By clause, all items appearing in the Select list must also appear in the Group By list, except the aggregate functions. Rewrite your query as:
SELECT SensorID, State, NumKey, MAX(DateTimeEvent)
FROM Sensors
GROUP BY SensorID, State, NumKey