Jump to content


Photo

Best Way To Retrieve Last Row


  • Please log in to reply
3 replies to this topic

#1 frustrated

frustrated
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 24 January 2006 - 02:10 PM

Hello,

I'm trying to figure out what is the best or most optimzed way to go about the following situation. All I want to do is retrieve the last row inserted into a table. It happens that one of the fields in my table has a date field, so my plan was to do something like this.

$sql = "SELECT id, name, date FROM the_table ORDER BY date ASC LIMIT 1";

So I think that's pretty self explanatory but I'm sure that's probably not the best way to go about to retrieving the last record.

Anyone have any feedback on this topic for me?

#2 Honoré

Honoré
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationAntwerp - Belgium

Posted 24 January 2006 - 03:08 PM

[!--quoteo(post=339396:date=Jan 24 2006, 04:10 PM:name=frustrated)--][div class=\'quotetop\']QUOTE(frustrated @ Jan 24 2006, 04:10 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I'm trying to figure out what is the best or most optimzed way to go about the following situation. All I want to do is retrieve the last row inserted into a table. It happens that one of the fields in my table has a date field, so my plan was to do something like this.
$sql = "SELECT id, name, date FROM the_table ORDER BY date ASC LIMIT 1";
[/quote]
If you want the last inserted row you probably should use descending order on date:
$sql = "SELECT id, name, date FROM the_table ORDER BY date DESC LIMIT 1";


#3 frustrated

frustrated
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 24 January 2006 - 05:02 PM

Yea you're right about that. I always confuse ASC and DESC. I'm still wondering if there is a better or quicker way to get the last inserted row.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 January 2006 - 07:29 PM

First, if you're just inserted this row, you can use LAST_INSERT_ID() to get that uid and do a quick lookup. Second, if this is not the case, you should be using the UID field to find the newest record -- just realize that this is _not_ a safe way to find the next uid, since this is not an ACID operation.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users