Problem with MAX() and row data

[pillsbur]

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]

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]

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]

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]

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]

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]

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