homepage Welcome to WebmasterWorld Guest from 54.227.12.219
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved