Get most recent record

[proggybilly]

I am working on a program for monitoring my server backups. I am filling a table with the server and backup info every time the backup runs. So, I am going to have multiple records for each server. Each server has a different backup recurrence, some are 2 days some are 3 days so going strictly by date does not work here. I can display all my records sorted by a time stamp for the record showing recent dates first. (order by date DESC). But what I want is instead of seeing:

server1 2009-10-16
server2 2009-10-16
server1 2009-10-15
server2 2009-10-15
server3 2009-10-15
server1 2009-10-14

I only want to see the most recent record for each server so I would wanna see

server1 2009-10-16
server2 2009-10-16
server3 2009-10-15

Not sure if I am explaining it correctly. But I am needing help with how to phrase my select statement.

 

[r937]

SELECT server
     , MAX(date) AS last_backup
  FROM daTable
GROUP
    BY server

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[proggybilly]

Thanks for your help, that is getting me closer however I am still seeing duplicate rows for each server.

dmz  201M  382496 2009-10-19 06:00:08

dmz  201M  382122	2009-10-15 14:53:33

guardian 150M 294084	2009-10-19 06:00:09

guardian 149M 294422 2009-10-16 09:29:35

mail0 323M 606306 2009-10-19 06:00:10

mail0 321M 601382 2009-10-15 14:53:35

mail1 620M 1147382 2009-10-19 06:00:13

mail2 881M 1685932 2009-10-19 06:00:17

moya2 12G 24033756 2009-10-19 06:01:15

moya2 13G 25816807 2009-10-16 09:32:40

I only want the most recent event for each server.

 

[r937]

i don't know what query you used that produced "duplicates" but the query i gave you does not

in post #1 there are only two columns in your table, so that's what i geared the solution to

do a search for "groupwise maximum"



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[proggybilly]

I took what you gave and added a couple more fields out of the table that just contain some extra info. But, I ran what you gave exactly as you gave it, and still get doubles. Maybe I have something in my table that is keeping it from coming out right.

 

[proggybilly]

Disregard, I figured out that for some reason, a newline character is being added to the end of server name when it gets inserted into the database. That is why it is appearing to have duplicates. I am working on correcting this now.

 

[proggybilly]

Ok, so using your method. I attempt to pull all necessary data that I need to display. I am getting the most recent record per server based on the date like your query should do. However when I add the other fields into my query, the data doesn't match up. Lets say I am using fields:

server
filename
size
num_files
start
finish
total_time
date

server and date are doing like the query should, but the other fields do not match. See example:

| dmz      | dmz200910151005.tbz2       | 201M  |    382122 | 4:59       | Thu Oct 15 10:05:01 CDT 2009 | Thu Oct 15 10:10:00 CDT 2009 | 2009-10-19 06:00:08 |
| guardian | guardian200910151035.tbz2  | 149M  |    294422 | 4:1        | Thu Oct 15 10:35:11 CDT 2009 | Thu Oct 15 10:39:12 CDT 2009 | 2009-10-20 06:00:21 |
| mail0    | mail0200910142013.tbz2     | 321M  |    601382 | 15:39      | Wed Oct 14 20:13:30 CDT 2009 | Wed Oct 14 20:29:09 CDT 2009 | 2009-10-19 06:00:10 |
| mail1    | mail1200910142010.tbz2     | 620M  |   1147382 | 53:13      | Wed Oct 14 20:10:50 CDT 2009 | Wed Oct 14 21:04:03 CDT 2009 | 2009-10-19 06:00:13 |
| mail2    | mail2200910142008.tbz2     | 881M  |   1685932 | 32:30      | Wed Oct 14 20:08:31 CDT 2009 | Wed Oct 14 20:41:01 CDT 2009 | 2009-10-19 06:00:17 |

You can see the start and finish (which are fed into database from a text file and are a varchar)do not match the date which is a timestamp field. Not sure if I am making myself clear or not but basically I need all the pertinant info from the row containing the most recent record.

 

[r937]

yes, very clear -- it is a well-known problem

simply adding additional columns to the SELECT clause without adding them to the GROUP BY clause is the source of the problem

this is invalid SQL (according to the SQL standards) but mysql lets you do it anyway

mysql engineers have a good reason for this "enhancement" but i won't bother you with the details

suffice to say that most mysql developers are not aware of this nuance, and are then (rightfully) frustrated when the query runs but returns the wrong data

take the query i gave you in post #2, and wrap parentheses around it, and join this back to your main table like so --

SELECT t.* -- entire row
FROM ( MAX query goes here ) AS m
INNER
JOIN daTable AS t
ON t.server = m.server
AND t.date = m.last_backup



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon