SQL Query - Choosing One of Many

How best to do?

[Nick_W]

Hi all,

If I'm going to be selecting from a table where i may get multiple rows but want only the the one with the latest timestamp, is there a way to do this with the SQL rather than selecting all and processing the results with PHP?

Query would look like this:

SELECT * FROM table WHERE identifier = '23';

A timestamp is included in each row and I'd just like the row with the latest timestamp.

Possible to do just on the SQL side?

Many thanks!

Nick

[jpjones]

Try something along the lines of:

SELECT * FROM table WHERE identifier = '23' order by timestamp_field desc limit 1;

That will return only one result, which would be the most recent in the table. To verify this, change limit 1 to limit 5 and compare the output.

HTH,
JP

[aspdaddy]

If your version of SQL supports max:

SELECT *
FROM table
where identifier in (select max(identifier) from table)

[jpjones]

Note my suggestion only works in MySQL. For Access, and (maybe) MSSQL (I haven't used it so don't know if it supports the terminology), remove limit X, and replace it with

SELECT TOP X * From TblBlahBlah

JP

[Nick_W]

SELECT * FROM table WHERE identifier = '23' order by timestamp_field desc limit 1;

Sheeesh! What an idiot!

Here's me, as usual, making life complicated for myself when there's a perfectly simple and (should have been) obvious solution ;)

Not sure on MAX - MySQL....

Thanks very much indeed guys..

Nick