| Problem with MAX() and row data
|
pillsbur
msg:3776204 | 8:47 pm on Oct 29, 2008 (gmt 0) |
Hi, I'm getting some strange results in a query that uses MAX(). Basically, other parts of my SELECT are being populated with values that are not all from the same row. Here's my query:
SELECT IC_computer, MAX(CONCAT_WS('-',IC_date,IC_time)) AS IC_datetime, IC_status
FROM usagestats
WHERE IC_computer LIKE 'IC1%'
GROUP BY IC_computer Here's some of the raw mysql data. I've sorted it by date DESC and time DESC to make it easier to read:
************************************************
*IC_computer ** IC_date ** IC_time ** IC_status*
************************************************
IC101 ** 10/29/2008 ** 12:53:11.09 ** OUT
IC101 ** 10/29/2008 ** 12:39:21.92 ** IN
IC101 ** 10/29/2008 ** 10:55:57.72 ** OUT
IC101 ** 10/29/2008 ** 10:41:06.52 ** IN
IC102 ** etc, etc, etc
IC103 ** etc, etc, etc My query returns this:
******************************************
*IC_computer ** IC_datetime ** IC_status *
******************************************
IC101 ** 10/29/2008-12:53:11.09 ** IN Note that the IC_datetime is correct -- it is indeed the most recent entry for this particular computer. But why is the IC_status value incorrect? Why isn't the value of IC_status that comes from the same row as the correct IC_datetime being used? Where is that 'IN' coming from in the query results? What am I not understanding about MAX()? Thanks for any assistance! Glenn p.s. I didn't design the database structure and don't have the power to redo it properly. I'm stuck with what you see here.
|
LifeinAsia
msg:3776236 | 9:20 pm on Oct 29, 2008 (gmt 0) |
MAX() is an aggregate function and doesn't give you specific rows back. (In fact, some DBs won't even run the query.) Try something like:
SELECT IC_computer, CONCAT_WS('-',IC_date,IC_time) AS IC_datetime, IC_status
FROM usagestats
WHERE IC_computer LIKE 'IC1%'
AND CONCAT_WS('-',IC_date,IC_time) = (SELECT MAX(CONCAT_WS('-',IC_date,IC_time)) FROM usagestats)
|
pillsbur
msg:3776266 | 10:00 pm on Oct 29, 2008 (gmt 0) |
Thanks for the reply. Unfortunately I'm more confused now. If MAX() doesn't return specific rows, what does it return? I need to use something like MAX() because I need the GROUP BY feature in order for my query result to be an array of each IC_computer and its status. The query you suggested only returns a single row -- not sure why though. Thanks for your continued help. Glenn
|
LifeinAsia
msg:3776276 | 10:20 pm on Oct 29, 2008 (gmt 0) |
| If MAX() doesn't return specific rows, what does it return? |
|
It returns a value. | I need ... my query result to be an array of each IC_computer and its status. |
|
Then you need to use the following:
SELECT IC_computer, MAX(CONCAT_WS('-',IC_date,IC_time)) AS IC_datetime, IC_status
FROM usagestats
WHERE IC_computer LIKE 'IC1%'
GROUP BY IC_computer, IC_status
|
pillsbur
msg:3776882 | 5:13 pm on Oct 30, 2008 (gmt 0) |
Hmm. Interesting result when there are two GROUP BY elements: IC101 IN 10/29/2008-18:49:43.22
IC101 OUT 10/29/2008-21:23:10.00
IC102 IN 10/29/2008-19:39:40.66
IC102 OUT 10/29/2008-23:03:13.54
<snip>
IC108 IN 10/30/2008-9:01:28.46
IC108 OUT 10/30/2008-9:00:43.97 So, now my results are an array with TWO entries per computer. The IC108 entry makes me suspect it's the latest IN and the latest OUT, but what I need is the latest date-time entry for each computer and the value of the status for that date-time. Maybe MAX() isn't the way to do this after all? Ultimately, I could go a different route completely and query the data for each individual workstation and sort by date then time, LIMIT 1, but I'd rather not do 62 queries when I can do one. Any further ideas? Thanks again,
Glenn
|
LifeinAsia
msg:3776945 | 6:13 pm on Oct 30, 2008 (gmt 0) |
| So, now my results are an array with TWO entries per computer. |
|
Right... You DID say | I need the GROUP BY feature in order for my query result to be an array of each IC_computer and its status. |
|
Each computer has 2 statuses: IN, OUT. So you will get 2 entries for each computer. If you want the current status for each computer, you can do some parsing of the data you get from the query.
|
pillsbur
msg:3777541 | 3:56 pm on Oct 31, 2008 (gmt 0) |
I now see how MAX() does and does not work -- I was misunderstanding (or not understanding completely) the various web tutorials I ran across. Yes, I could do some more parsing to get two rows down to one, but I think for this situation (given the way the database has been built) I'm going to take the easy way out and just do 62 queries. Not as fast, I know. Thanks for your help! Glenn
|
|
|
