Getting last record from a table

Anon

I need a fast (efficient) way to retrieve the last record in a table.

I have a table that is used to record real time data. I want to display, on a web page, the last entry made in the table. In other words, the most recently recorded record. While a timestamp field is available, I’m hoping there is a command or query to get the last record. The tables are very large (some greater than 3 million records) so I need speed.

Anon

I can't think of one, but if you index the timestamp field and then use MAX() to get the unique record_id then you can just select that record.

You can do this in 2 queries, or 1 depending on what database you are using, and how well it handles subqueries.

Anon

Try

SELECT * FROM TABLE ORDER BY ID DESC LIMIT 1

Anon

Using mySQL:

Is there a method without using an index?

Thank you

Anon

Many databases automatically create a unique index number for every record, usually called something like RECORD_ID. You should look at your database documentation and see if the one you are using does this.

Without some external way of sequencing your data, and without indexes you are looking at a very long query.

select * from table where timestamp in (select max(timestamp) from table);

select max(timestamp) from table;
store in variable $max;
select * from table where timseamp = $max;

Anon

I think you should download the PHP manual at www.php.net

Timothy Foster wrote:

I need a fast (efficient) way to retrieve the last record in a table.

Anon

select * from table order by timestamp desc limit 1;

Anon

You literally never need an index to make a query work. Indexes are created to (1) increase SELECT performance, and (2) enforce uniqueness constraints (though I suppose you could say this is just a special case of #1)

Anon

I'll agree with that one. It's the fastest your going to get, probably.

---John Holmes...