Why mysql query is returning wrong Results?

Question

I am attempting to find the latest log entries in my MySQL database. I cooked up this query late last night but upon testing today it does not seem to be returning the correct data.

SELECT MAX(id), hostName, email, info, time 
FROM log
WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
GROUP BY hostName 
ORDER BY `time` DESC

The query runs just fine, but none of the other fields seem to match the id column. It grabs the max id number, but the hostName, email, and info do not match the id, and they are not the latest. I've spent a few hours spinning my wheels on this (I'm a MySQL noob and just doing this for fun) so I'm pretty confused now..

Thanks for any help!

EDIT: Wow thanks for all the responses! Sorry for the confusion, I should have said "I want the latest log per hostname!" <- That is specifically what I need.

Why do you need the MAX()? Is ordering by time not enough to get the latest log entries? — Dan Smith
– Dan Smith, Commented Jun 15, 2012 at 13:35
Hmm.. just removed it and I get data that's almost 30 mins old.. — David
– David, Commented Jun 15, 2012 at 13:37
Can you explain the result you want ? "Only the last entry of each host and id" ? — Akarun
– Akarun, Commented Jun 15, 2012 at 13:39
Edited main post. I want the latest log entry per each hostname — David
– David, Commented Jun 15, 2012 at 13:42
Something to note about all the index suggestions: while they will help your read performance (i.e. running this query), they will most likely hurt your insert performance. If you're inserting a LOT of rows into this log table, the index maintenance may not be worth saving you the time to run the query. — Ben Mosher
– Ben Mosher, Commented Jun 15, 2012 at 14:02

Mosty Mostacho · Accepted Answer · 2012-06-15 13:50:20Z

2

You said:

I am attempting to find the latest log entries in my MySQL database

There is no need to group nor use a subquery. Just try this:

SELECT id, hostName, email, info, time FROM log
WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
ORDER BY `time` DESC

Now you said:

I want the latest log per hostname!

Then the solution should be this:

SELECT l1.id, l1.hostName, l1.email, l1.info, l1.time FROM log l1
LEFT JOIN log l2 on l1.hostName = l2.hostName AND l1.time < l2.time
WHERE l2.time IS NULL
ORDER BY l1.time DESC

edited Jun 15, 2012 at 13:50

answered Jun 15, 2012 at 13:40

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

Thank you, but I am looking specifically for one log per hostname!

David Over a year ago

I know, I am not good at explaining things I don't know well embarrased

Community · Accepted Answer · 2017-05-23 12:03:14Z

1

Another way to do the same thing is to GROUP BY and then JOIN to the log table:

If "latest" means the maximum id, use a (hostname, time, id) index and this:

SELECT log.id
FROM 
        log
    JOIN 
        ( SELECT MAX(id) AS id
          FROM log
          WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
          GROUP BY hostName
        ) AS grp
      ON grp.id = log.id ;

UPDATE: You may alo want to try this one. It just might be more efficient (depending on the hostname distribution) - and would benefit from a index: (hostname, id, time) index:

SELECT log.id
FROM 
        log
    JOIN 
        ( SELECT MAX(id) AS id
          FROM log
          GROUP BY hostName
        ) AS grp
      ON grp.id = log.id 
WHERE log.time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) ;

If "latest" means the maximum time (e.g. if time is insertion_time and is not changed later), use a (hostname, time) index and this:

SELECT log.*
FROM 
        log
    JOIN 
        ( SELECT hostname, MAX(time) AS max_time
          FROM log
          WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
          GROUP BY hostname
        ) AS grp
      ON  grp.hostname = log.hostname
      AND grp.max_time = log.time ;

And finally, since everyone forgot to answer the "Why mysql query is returning wrong Results?" question, here is another question with some useful answers: Why does MySQL add a feature that conflicts with SQL standards?

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Jun 15, 2012 at 13:49

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

12 Comments

David Over a year ago

Thank you, I believe I have an index on id as it is the primary key. I'm looking into adding one on hostname now! :)

ypercubeᵀᴹ Over a year ago

Not on (hostname) but on (hostname, id)

ypercubeᵀᴹ Over a year ago

Hm, I didn't think of the time condition. This is not going to be easy.

ypercubeᵀᴹ Over a year ago

I re-read your question. My previous query was wrong. See my edit.

ypercubeᵀᴹ Over a year ago

Does "latest" means the row with the maximum id or the one with the maximum time?

|

Ben Mosher · Accepted Answer · 2012-06-15 13:39:22Z

1

If you want the full row that corresponds to the max id for each distinct hostName,

SELECT id, hostName, email, info, time
FROM log
WHERE id IN 
    (SELECT MAX(id) FROM log WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
     GROUP BY hostName)
ORDER BY `time` DESC

answered Jun 15, 2012 at 13:39

Ben Mosher

13.4k9 gold badges71 silver badges80 bronze badges

3 Comments

David Over a year ago

That query took 177 seconds to run O_O

Olivier Coilland Over a year ago

Do you have the helpful index on hostName ?

David Over a year ago

Negative. I've heard about indexes and really should start researching them.. I'll try and add one..

Olivier Coilland · Accepted Answer · 2012-06-15 13:39:13Z

0

This is a famous one:

SELECT *
FROM log
WHERE log.id IN (
    SELECT MAX(id)
    FROM log
    WHERE time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
    GROUP BY hostName) T

answered Jun 15, 2012 at 13:39

Olivier Coilland

3,11618 silver badges20 bronze badges

Collectives™ on Stack Overflow

Why mysql query is returning wrong Results?

4 Answers 4

2 Comments

12 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

12 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related