How can I select specific rows in a MySQL Table? So instead of selecting ... ORDER BY date can I do something like SELECT * FROM Posts FROM(5) TO(10) ORDER BY date DESC where I would be selecting Posts 5 to 10 in the descending order?
-
1000 rep and no searches on google before? hmmdynamic– dynamic2011-06-13 17:29:00 +00:00Commented Jun 13, 2011 at 17:29
Add a comment
|
6 Answers
Your question is a little ambiguous. There are two possibilities:
You want only part of the results to be retrieved from the database, for example from the 5th one to the 10th one:
SELECT * FROM `posts` ORDER BY `date` DESC LIMIT 6 OFFSET 4which will skip the 4 first results and given you next 6 results (beginning with 5th from the original set and ending with 10th from the original set).
You want results with specific IDs between
5(inclusive) and10(inclusive):SELECT * FROM `posts` WHERE `id` BETWEEN 5 AND 10 ORDER BY `date` DESCor (which can be useful if the list of IDs is not the whole range):
SELECT * FROM `posts` WHERE `id` IN (5,6,7,8,9,10) ORDER BY `date` DESCbut this last example is only to show you different way of picking "specific" rows.
Comments
use limit:
SELECT * FROM Posts ORDER BY date DESC LIMIT 5, 5
1 Comment
mkilmanas
You can use limit operator in mysql to do this. Ex: SELECT * FROM posts LIMIT 5 , 5;
Comments
Alternative Syntax which is more readable in my opinion:
SELECT * FROM Posts ORDER BY date DESC LIMIT 5 OFFSET 5
The first number indicates the number of results returned, the second one defines the offset from where to begin with.
If you use the LIMIT 5,5 it's the other way round.
1 Comment
Johan
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY date DESC' at line 1
You can use the limit:
SELECT * FROM tabel ORDER BY date DESC LIMIT 5,5
(5th till 10th record based on descending date).
2 Comments
mkilmanas
Please read that help content regarding code inclusion in the answer
Joshua - Pendo
worthless giving a -1 for that tho.
SELECT * FROM Posts ORDER BY date DESC limit 5, 10
2 Comments
mkilmanas
Please read that help content regarding code inclusion in the answer
Amal