SQL Query to get latest but unique records

Hi there

I have a table which records the network interface details for a particular box on my network. There is a time stamp on each record and the table also contains historical information which occasionally may get called on…

I am trying to figure out how I can structure a query so that i get only the latest records for each unique interface but only for my host

[U][B]HOST[/B]		[B]NIC[/B]	[B]IP[/B]		[B]DATESTAMP[/B][/U]		
server1		BGE0	1.1.1.1		10-JUN
server1		BGE0	1.1.2.1		09-JUN
server1		BGE1	2.2.2.2		10-JUN
server2		CE0	3.3.3.3		6-APR
server1		BGE1	2.2.2.1		11-JUN
server2		CE0	3.3.3.1		5-MAR
...
...

So for example, i want to issue an SQL statement that effectively does this

SELECT * FROM table WHERE HOST = "server1" \\
AND DATESTAMP is latest for each unique NIC

I know the statement above is garbled rubbish, but basically I want it to give me the latest record for each NIC that may be installed on that system… ( I will have no predetermined way of knowing how many that may be, for example, the interfaces on that box could go up to BGE7 ! ).

So for example, with my only criteria being the HOST (in this case server1) i would get the following results, which constitute the latest record for each unique network interface on that server)

[U][B]HOST[/B]		[B]NIC[/B]	[B]IP[/B]		[B]DATESTAMP[/B][/U]	
server1		BGE0	1.1.1.1		10-JUN
server1		BGE1	2.2.2.1		11-JUN

as you can see, older records for BGE0 and BGE1 on server1 are not retrieved

Does anybody have any idea how i would do this, or indeed is it even achievable at all ?

any help or guidance with this would be greatly appreciated

Cheers

SELECT t1.*
FROM table t1
INNER JOIN
  (SELECT
       HOST
     , NIC
     , MAX(DATESTAMP) AS MAXDATESTAMP
   FROM table
   GROUP BY HOST, NIC
  ) AS t2
ON t1.HOST = t2.HOST
AND t1.NIC = t2.NIC
AND t1.DATESTAMP = t2.MAXDATESTAMP
WHERE t1.HOST = "server1"

wow thank you very kindly for your response. It seems as though my issue is a lot more complex than origially though. I ran your query in my phpmyadmin sql runner window and it came back with

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table t1 INNER JOIN (SELECT HOST , NIC , MAX(DATESTAMP) AS MA' at line 2

Highlighting the word “MAX” from the string “MAX(DATESTAMP)” in red

by trying to understand your code, it seems you are creating a virtual table to perform comparisons against within the SQL (which is very clever). Is there anything i need to do to my database/table to prepare for this code ? i.e create a t1 or t2 ??

done a bit of looking around and it seems that MAX is an oracle function …is this available in mysql 5 as well?

MAX is standard SQL and is available in every database system

your problem is that you think you have a table called table

guido’s code simply uses the same name that you mentioned in post #1

try replacing the table name in guido’s query with the actual name of your table

my apologies guys :blush: I changed the table name in the middle but not on line 2

SELECT t1.*
FROM network t1
INNER JOIN
  (SELECT
       HOST
     , NIC
     , MAX(DATESTAMP) AS MAXDATESTAMP
   FROM network
   GROUP BY HOST, NIC
  ) AS t2
ON t1.HOST = t2.HOST
AND t1.NIC = t2.NIC
AND t1.DATESTAMP = t2.MAXDATESTAMP
WHERE t1.HOST = "server1"

gives me the result i want

I dont quite understand what is happening in the query, but it has given me the impetus to go and work it out

thanks for your help again

run the subquery by itsef, and inspect the results

now imagine that those results were an actual table that you could join your network table to

pay particular attention to the join conditions

simple, yes?

:slight_smile: