CODEWITHSUNDEEP
phpmysqlsqlmax
antonpuz
antonpuz

Reputation: 3316

getting data from table where one field is maximum in one query

i want to get data from my database where one field is max, at this moment i do this in 2 queries. the thing is i dont want to overload the server so i am looking for a way to make it in 1 query. any suggestions? as you can see i am looking for entry where the timestamp is max.

    $query = "SELECT MAX(TIMESTAMP) AS timestamp FROM `data`";
$run_query = mysql_query($query);
$highest = mysql_result($run_query,'0','timestamp');

$query = "SELECT * FROM `data` where `timestamp`='$highest'";
$run_query = mysql_query($query);

thanks in advance.

Upvotes: 0

Views: 77

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If your goal is to minimize server resources, the difference between one query and two queries is really minor. The engine needs to do pretty much the same work. The difference would be the slight overhead of compiling two queries rather than one.

Regardless of the solution, you will minimize server resources by building an index on data(timestamp).

Upvotes: 0

Marc B
Marc B

Reputation: 360572

An alternative, if you can guarantee that there will never be two records with the same timestamp:

SELECT *
FROM data
ORDER BY timestamp DESC
LIMIT 1

If you can have duplicate timestamps, then the other answers with the sub-select are the better solution.

Upvotes: 1

Skinny Pipes
Skinny Pipes

Reputation: 1035

SELECT  * FROM `data` WHERE `timestamp` = (SELECT MAX(`timestamp`) FROM `data`)

Upvotes: 1

John Woo
John Woo

Reputation: 263693

This will simply work as you desired.

SELECT  *
FROM    data
WHERE   timestamp = (SELECT MAX(timestamp) FROM data)

Backticks on this case are optionals. But actually timestamp is a reserved keyword but is permitted to be used even without escaping it with backtick.

Upvotes: 1

Related Questions