mysql query for table row range

Question

This might be a very basic question but I am struggling with queying the specific rows in a table based only on the row range.

Let's say I have a table ABC where I have populated 1000 rows. Now I want a sql query so that I can fetch first 100 rows ( that is the range 1 to 100) and then the next 100 ( 101 to 200) and so on till I am done with all rows. And this should be done without querying/filtering on the table's id or any column id.

I am not able to figure it out as I am trained only on querying specific columns in WHERE clause so would appreciate if someone can plz help

To query 100 pseudo random rows, SELECT * FROM Table1 LIMIT 100. If you want them to be deterministic, you also need to order by something. — Joachim Isaksson
– Joachim Isaksson, Commented May 22, 2013 at 19:07
Thanks, but what is this semi random thing? and how do I keep track of the starting row for my next query — Shyam
– Shyam, Commented May 22, 2013 at 19:09
If you don't order by anything, the rows can come in any order, even different order per query. You can give a second parameter to LIMIT to start at a certain offset, you may want to look it up in the manual. — Joachim Isaksson
– Joachim Isaksson, Commented May 22, 2013 at 19:11
@JoachimIsaksson - Thanks for your suggestions. This gives me a starting point to explore the further, Thanks a lot — Shyam
– Shyam, Commented May 22, 2013 at 19:17

rink.attendant.6 · Accepted Answer · 2013-05-22 19:12:48Z

18

You have to use the LIMIT clause in the SELECT query. MySQL allows you to set two parameters for the clause, the offset (first parameter) and the number of rows to fetch (second parameter).

SELECT * FROM `ABC` LIMIT 0, 100
SELECT * FROM `ABC` LIMIT 100, 100
SELECT * FROM `ABC` LIMIT 200, 100
-- etc...

However, you cannot guarantee the order of these rows unless you sort by one or more specific column(s) using the ORDER BY clause.

Read more about the SELECT statement here: http://dev.mysql.com/doc/refman/5.6/en/select.html

answered May 22, 2013 at 19:12

rink.attendant.6

46.7k64 gold badges111 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Faishal · Accepted Answer · 2013-05-22 19:14:16Z

4

you can use limit in mysql.

limit accept 2 parameters.

this will return 1-10 records.

select * from abcd limit 10

this will return 10-20 records.

select * from abcd limit 10,10

this will return 20-30 records.

select * from abcd limit 20,10

answered May 22, 2013 at 19:14

Faishal

1,4931 gold badge14 silver badges23 bronze badges

3 Comments

WBT Over a year ago

There is a significant difference between "20-30 records" (25 +/5 semi-randomly selected records) and "records 20-30" (11 records numbered 20 to 30 inclusive)

inetphantom Over a year ago

As @WBT pointed out this is still wrong here in 2019. You get records 10 to 20 and not 10 to 20 records.It's even worse, because you actualy get records 10 to 19 (0 based numbering)

user3481644 Over a year ago

Agree with the other comments "X - Y records" specifies a range of the number of records, hence "10-20 records" sounds like you would get anywhere from 10 to 20 records returned. This is very poorly phrased.

Collectives™ on Stack Overflow

mysql query for table row range

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related