Welcome to WebmasterWorld Guest from 54.144.243.34

Forum Moderators: open

Problem with MAX() and row data

   
8:47 pm on Oct 29, 2008 (gmt 0)

5+ Year Member



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.

9:20 pm on Oct 29, 2008 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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)

10:00 pm on Oct 29, 2008 (gmt 0)

5+ Year Member



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

10:20 pm on Oct 29, 2008 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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
5:13 pm on Oct 30, 2008 (gmt 0)

5+ Year Member



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

6:13 pm on Oct 30, 2008 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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.

3:56 pm on Oct 31, 2008 (gmt 0)

5+ Year Member



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

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month