Silly question - Getting the rows A thru B in MySQL

Hi everyone,

Suppose I have a lot of rows in a table, and I want to select rows number A through B in terms of recentness, how could I do that? E.g., select the 10th, 11th, 12th, ..., and 20th most recent rows, for example.

Thanks for helping

 

You need to use ORDER BY and LIMIT (mention the number of rows you need). e.g if you are deciding the recentness on the basis of time then the query will be

SELECT * FROM table_name
ORDER BY date_time DESC
LIMIT 10

Code (markup):

But if you need 11th, 12th, ... and 20th most recent rows then use LIMIT in this way

SELECT * FROM table_name
ORDER BY date_time DESC
LIMIT 10, 10

Code (markup):

 

Thanks very much I guess I will need to teach myself how dates are stored in SQL instead of storing them all as varchar's of PHP's date('l jS \of F Y h:i:s A')

 

You can workaround this if you have an AUTO_INCREMENT column by using that column in ORDER BY clause. But i recommend you to store the dates in proper DATE and TIMEdatatype of mysql.

 

Thanks for the help I've made the switch to DATETIME columns. I love learning new things about programming....

Sending a little love to your geosciences page with my (weak) stumbleupon

 

Thanks glowing face man.