mysql help : query with 2 conditionals

Shell Programming and Scripting
1

Hi there, I have a table that stores multiple records for many different servers, each of which is timestamped ... I wanted to write a query that would enable me to only output the "latest" record (based on timestamp) for each "unique" server. So for example my main table looks like this

SERVER		NIC	IP		DATESTAMP		
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 the query would get only the latest record for each server. so the output would be

SERVER		NIC	IP		DATESTAMP		
server2		CE0	3.3.3.3		6-APR
server1		BGE1	2.2.2.1		11-JUN

Ive been playing around and am not quite sure how i integrate two conditionals inside a single statement, i.e. 1) unique server 2) latest timestamp

apologies for the nooby question but im new to this and any guidance would be great

2

some clues for you.

First to convert the date (for example, change from 11-JUN to 11-06) will be more easy for sort -k

3 
mysql>
mysql> select * from t;
+---------+------+---------+------------+
| server  | nic  | ip      | datestamp  |
+---------+------+---------+------------+
| server1 | BGE0 | 1.1.1.1 | 2009-06-10 |
| server1 | BGE0 | 1.1.2.1 | 2009-06-09 |
| server1 | BGE1 | 2.2.2.2 | 2009-06-10 |
| server2 | CE0  | 3.3.3.3 | 2009-04-06 |
| server1 | BGE1 | 2.2.2.1 | 2009-06-11 |
| server2 | CE0  | 3.3.3.1 | 2009-03-05 |
+---------+------+---------+------------+
6 rows in set (0.00 sec)

mysql>
mysql>
mysql> select * from t where (server, datestamp) in
    -> (select server, max(datestamp) from t group by server);
+---------+------+---------+------------+
| server  | nic  | ip      | datestamp  |
+---------+------+---------+------------+
| server2 | CE0  | 3.3.3.3 | 2009-04-06 |
| server1 | BGE1 | 2.2.2.1 | 2009-06-11 |
+---------+------+---------+------------+
2 rows in set (0.01 sec)

mysql>
mysql>

tyler_durden

4

thank you Tyler , much appreciated ....