CODEWITHSUNDEEP
phphtmlmysql
Glen P
Glen P

Reputation: 11

SQL Query matching two fields

I currently have a table called cc_site, in this table I store the results of pings from different ip addresses.

The table has four fields:

I'm trying to create a query which will give me the latest result based off the date field for an individual ip address. The code I'm currently using is below but it doesn't work unless the IP I'm using in the query is the latest entry in the table.

$query = "SELECT *
          FROM cc_site
          WHERE ip='$host'
          AND Date IN (
              SELECT max(date)
              FROM cc_site
          )";

I knew the query is incorrect however I have not been able to find code that would perform the query I need.

Any help would be great.

Upvotes: 1

Views: 43

Answers (2)

asha
asha

Reputation: 1

select * from cc_site 
where ip='$host' and date in(select max(date) from cc_site where ip='$host');

I think this might give you the answer

Upvotes: 0

Sougata Bose
Sougata Bose

Reputation: 31739

No need of that sub-query. Use can use ORDER and LIMIT instead - 

SELECT * FROM cc_site WHERE ip='$host' ORDER BY `date` DESC LIMIT 1

This will sort the records in descending order according to date and return the record with highest record.

Upvotes: 4

Related Questions