CODEWITHSUNDEEP
pythonmysql
user1538709
user1538709

Reputation:

Retrieving only the most recent row in MySQL

I have a mysql table with coloumns of name, perf, date_time . How can i retrieve only the most recent MySQL row?

Upvotes: 3

Views: 1675

Answers (6)

Chris
Chris

Reputation: 342

Are date and time separate fields? And would you explain what you mean by "most recent"? If date and time are separate fields or are not refererring to user activity and you mean "most recently updated by user" than you might want to know that time-sensitive tables usually contain a field like "updated_at" which carry a mysql standard timestamp, e.g. '1989-11-09 12:34:32'. Perhaps you should consider introducing a field like this into your schema. You could than set triggers to update the datetime information like so:

CREATE TRIGGER mytable_update BEFORE UPDATE ON `mytable`
FOR EACH ROW SET NEW.updated_at = NOW();

Of course you could than retrieve the record which has been updated most recently by a select statement as answered above:

ORDER BY `updated_at` DESC LIMIT 1;

In addition, time-sensitive tables usually have a field 'created_at' which carries an unchanging timestamp set when the row is inserted. It is often connected to a trigger like so:

CREATE TRIGGER mytable_create BEFORE INSERT ON `mytable`
FOR EACH ROW SET NEW.created_at = NOW(), NEW.updated_at = NOW();

The first trigger above should reflect this and carry the created_at information with an additional statement:

NEW.created_at = OLD.created_at;

If you use a framework (like Django, as you tagged Python), this could be handled by the ORM.

Upvotes: 2

NG.
NG.

Reputation: 6053

select top 1 * from tablename order by date_and_time DESC (for sql server)

select * from taablename order by date_and_time DESC limit 1(for mysql)

Upvotes: -1

Marc B
Marc B

Reputation: 360702

As an alternative:

SELECT *
FROM yourtable
WHERE date_and_time = (SELECT MAX(date_and_time) FROM yourtable)

This one would have the advantage of catching the case where multiple rows were inserted very quickly and ended up with the same timestamp.

Upvotes: 4

Ted Hopp
Ted Hopp

Reputation: 234807

SELECT name,
       perf,
       date_and_time
FROM   table
ORDER  BY date_and_time DESC
LIMIT  1

Upvotes: 2

lc.
lc.

Reputation: 116498

...
ORDER BY `date and time` DESC
LIMIT 1

Note this is not the most recently added row, this is the row with the most "recent" value in the date and time column, which is what I assume you are looking for.

Upvotes: 3

Jason Swett
Jason Swett

Reputation: 45094

SELECT * FROM table ORDER BY date, time LIMIT 1

Upvotes: 3

Related Questions