Mysql - Showing the 1 latest entry from each unique field

Question

I fail at mysql, and could really do with some help. I don't know what it would be called, and all my attempts at using combinations of DISTINCT and GROUP BY are just not working out.

I have a table of server monitoring data with these columns:

nStatusNumber
Bandwidth
Load 
Users
ServerNumber 
DiskFree 
MemFree
TimeStamp

**nStatusNumber** - A unique number increasing for each entry

**ServerNumber** - A unique number for each server

For the top of my dashboard for this, I need to display the most recent report for each unique server.

// How many servers are we monitoring ?

$nNumServers = mysql_numrows(mysql_query("SELECT DISTINCT(ServerNumber) FROM server_status;"));

// Get our list of servers

$strQuery = "SELECT * FROM server_status ORDER BY nStatusNumber DESC limit ".$nNumServers.";";

And then loop through the results until we hit $nNumServers . This worked at first, until servers started going down/up and the report order got jumbled. Say theres 20 servers, the most recent 20 results aren't necessarily 1 from each server.

I'm trying to figure this out in a hurry, and failing at it. I've tried all sorts of combinations of DISTINCT and GROUP BY with no luck so far and would appreciate any guidance on what's probably an embarrassingly easy problem that I just can't see the answer to.

Thanks!

PS - Here's an example query that I've been trying, showing the problem I'm having. Check the "nStatusNumber" field, these should be showing the most recent results only for each server - http://pastebin.com/raw.php?i=ngXLRhd6

PPS - Setting max(nStatusNumber) doesn't give accurate results. I don't want some average/sum/median figure, I need the most recent ACTUAL figures reported by each server. Heres more example results for the queries: http://pastebin.com/raw.php?i=eyuPD7vj

so quick question, this bit "This worked at first, until servers started going down/up and the report order got jumbled. Say theres 20 servers, the most recent 20 results aren't necessarily 1 from each server." --- are you polling a database for server availability rather than polling the servers themselves? — Brandt Solovij, Dec 06 '12 at 06:52
I have a cronjob that ssh's into each server and collects data and posts that data to the database table. The dashboard is then supposed to show just the most recent results up top (failing), as well as each server having graphs and historical data available (works fine) — Adam Otf Dodson, Dec 06 '12 at 07:06
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Bill Karwin, Dec 06 '12 at 07:08
sure cool - but what if a server is "unaccountable" - maybe im misunderstanding the question but it seems like your asking how to deal with triaging server failure during polling of said server ? — Brandt Solovij, Dec 06 '12 at 07:10
If a server isn't responding, the script sends me an alert and it stops reporting new status updates until it returns. I just want the dashboard to show the last reported status of each individual server, the alerts saying it is currently down are displayed seperately. — Adam Otf Dodson, Dec 06 '12 at 07:17

Sashi Kant · Answer 1 · 2012-12-06T07:04:26.930

0

Try this ::

Select 
Select MAX(nStatusNumber) from table,
Bandwidth,
Load,
Users,
ServerNumber,
DiskFree,
MemFree,
MAX(`TimeStamp`)

from your table 
group by ServerNumber

edited Dec 06 '12 at 07:04

answered Dec 06 '12 at 06:54

Sashi Kant

13,277
9
44
71

Thanks, but this is similar to what I tried earlier and it doesn't work as expected. Looking at the timestamp field alone it looks like the most recent results, but nStatusNumber's are very low (1,2,3,4 etc) when the most recent results are up to about 50,000. Example response: http://pastebin.com/raw.php?i=ngXLRhd6 – Adam Otf Dodson Dec 06 '12 at 07:02
If you need the nstatusNumber to be the max, I have updated my query – Sashi Kant Dec 06 '12 at 07:04
I have posted the results of your query, as well as a simple query that shows the 20 most recent status reports . As you can see, the info returned in your query isn't accurate.. http://pastebin.com/raw.php?i=eyuPD7vj – Adam Otf Dodson Dec 06 '12 at 07:15

Tanzeel Kazi · Accepted Answer · 2012-12-06T08:58:32.357

For your purpose you need to find the row unique to a nServerNumber and TimeStamp. This is not as simple as just saying MAX(TimeStamp) as you need to find the row corresponding to it.

Although I am not an expert in SQL you can try this and see if it works.

SELECT A.nServerNumber, A.nStatusNumber, A.nVNStatsBandwidth, A.fLoad, A.nUsers,
    A.nUsersPaid, A.nServerNumber, A.nFreeDisk, A.nTotalDisk, A.nFreeMemory,
    A.nTotalMemory, A.TimeStamp
FROM server_status A
INNER JOIN
(
    SELECT nServerNumber, MAX(TimeStamp) as `TimeStamp`
    FROM server_status
    GROUP BY nServerNumber
) B
ON A.nServerNumber = B.nServerNumber
AND A.TimeStamp = B.TimeStamp
ORDER BY A.nServerNumber ASC;

This query will give you all the servers with their latest info. So if you want the total number of servers just run the mysql_numrows(...) function on this result and if you want the data just iterate through the same result (no need to fire two separate SQL queries).

Thank you for this, I ended up using a slight variation on this :) — Adam Otf Dodson, Dec 06 '12 at 09:38

Mysql - Showing the 1 latest entry from each unique field

2 Answers2