mysql help : query with 2 conditionals


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting mysql help : query with 2 conditionals
# 1  
Old 11-16-2009
mysql help : query with 2 conditionals

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

Code:
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


Code:
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  
Old 11-16-2009
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  
Old 11-16-2009
Code:
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  
Old 11-17-2009
thank you Tyler , much appreciated ....
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need wrapper around mysql query

Hello Friends, I am using check_mysql_query plugin in nagios to query database and get output. query is working fine but output which i am getting contain query. I want to remove query from output and give custom message which will be simple and understandable. Can you help me here with any... (5 Replies)
Discussion started by: ghpradeep
5 Replies

2. Programming

Need help in mysql query

Hi All, i have a table in mysql with the following data Table name Test Assettype Serial_No Status location Mouse 123456 In Stock chennai Mouse 98765 Allocated chennai Keyboard ... (2 Replies)
Discussion started by: venkitesh
2 Replies

3. Programming

mysql query help

Hello i have created mysql query to compare to values and get difference in percentage as following: SELECT file_name, 100 - ((100 * (SELECT file_count FROM xipi_files z WHERE x.file_group = z.file_group AND x.file_name = z.file_name AND z.insert_date = CURDATE( ) - INTERVAL 1 DAY)) /... (1 Reply)
Discussion started by: mogabr
1 Replies

4. Shell Programming and Scripting

mysql query in shellscript

Hi, I want to run below query on shellscript but having one problm. ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME" "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and Date='$latest Date';" ) here Date column contans different below dates 2011-12-01... (11 Replies)
Discussion started by: aish11
11 Replies

5. Web Development

mysql query help

hello all i have 2 columns every column in the following format column1 2011-04-01 11:39:54 column2 2019-02-03 00:00:00 i want get difference between above data as following 2 days 11:39 how to do so ? i tried many functions but nothing works please advice what is the query... (6 Replies)
Discussion started by: mogabr
6 Replies

6. Web Development

Complex MySQL Query(s)

Hi all, I have a bit of an issue, I am working on a bit of a CMDB for a friend, it's to do with real estate. Anyway, this is my situation. I have a table which contains all the properties (forsale, sold, etc) in the DB named "property". Now, this is what I want to achieve, I wish to... (5 Replies)
Discussion started by: STOIE
5 Replies

7. Shell Programming and Scripting

? parameter in mysql query

I am debugging some code and came across ? in the WHERE cause in a mysql query. Is this possible and what situations would this be used? SELECT ip, count FROM table WHERE domain=? thanks & regards (2 Replies)
Discussion started by: hazno
2 Replies

8. Programming

How to query one to many mysql

Hi there, I have a hierarchical database that include 4 tables. Table A is the parent of B, B is Parent of C, C is parent of D. If I want to query everything in D that is associated with A.name, how do I do that? Thanks! YanYan (0 Replies)
Discussion started by: pinkgladiator
0 Replies
Login or Register to Ask a Question