CODEWITHSUNDEEP
phpsqlmysql
Laz
Laz

Reputation: 6204

How to select the most recent 10 records

I have a mysql database. How do I select the most recent 10 records? Im not storing timestamps. But the most the recent records are the ones at the bottom rite? Also. How so I get the next ten , the next ten and so on on clicking a button. Kinda like a bunch of forum posts. The recent ones show up first.

Upvotes: 1

Views: 3338

Answers (3)

DRapp
DRapp

Reputation: 48139

In addition to what @BoltClock mentioned, prequerying the maximum ID might help the engine with what other records are retrieved... ie: if you have a million records, and most recent 10, I don't know if it will still try to query out the million, order them, and THEN dump it.. I would try something like

select STRAIGHT_JOIN
      YT.*
   from 
      ( select max( IDColumn ) as MaxOnFile
           from YourTable ) PreQuery,
      YourTable YT
   where 
      YT.IDColumn >= PreQuery.MaxOnFile -10
   order by
      YT.IDColumn DESC
   limit 10

However, if for some reason, records are allowed to be deleted, you may opt to subtract a little farther back than the -10... but at least this way, the system won't even TRY to process all the other records...

Upvotes: 1

Shakti Singh
Shakti Singh

Reputation: 86346

I believe you have an auto increment column as a primary key you can use this column and to order by desc 

select * from table order by id desc  limit 10

otherwise you have a very poor database design

Upvotes: 5

BoltClock
BoltClock

Reputation: 723468

If you have an AUTO_INCREMENT column you can order by that in descending order then limit by 10.

But I suggest you store timestamps and order by that instead so you know you're sorting your records according to date, and not some other value that coincides with date of insertion.

Upvotes: 3

Related Questions