Why OFFSET isn't working the way it is expected?
I had three data with where gender=female. I was trying to call those data with limit and offset (Since in future there might be more than 1000 data).
SELECT * FROM `users` WHERE `gender`='Female' ORDER BY `birth_year` DESC LIMIT 10 OFFSET 0
Here's my data (only adding three columns and all of their gender is female) :
| id | username | birth_year |
|---|---|---|
| 3 | Is | (null) |
| 4 | Kar | (null) |
| 5 | girlfriend | 2005 |
When I execute the above, I get three data (id=5,4,3). When I change the offset to 1 I get (id=4,3). For offset=2 (id=4). When I set offset=3,4,5,6..... I get nothing. But I was supposed to get nothing for id=1 and 2 also. (Here's the guide I followed, W3SCHOOLS guide says the same also). But when I set LIMIT=1, it works correctly. It doesn't even work for LIMIT=2. I also tried by removing ORDER BY ...... (I am using MySQL (Lampp) in Xubuntu)
I also tried the following method :
SELECT * FROM `users` WHERE `gender`='Female' ORDER BY `birth_year` DESC LIMIT 1, 10```
1 answer
The script is read like this :
SELECT * FROM `users` WHERE `gender`='Female' ORDER BY `birth_year` DESC LIMIT "take n rows" OFFSET "skip n rows"
So when I wrote
SELECT * FROM `users` WHERE `gender`='Female' ORDER BY `birth_year` DESC LIMIT 10 OFFSET 0
It was saying to skip 0 rows and take followed 10 rows. For OFFSET=1, it was skipping only id=3 and taking other 10 rows.
So the script should be like this :
SELECT * FROM `users` WHERE `gender`='Female' ORDER BY `birth_year` DESC LIMIT 10 OFFSET 0
Then OFFSET will increase like this : 10, 20, 30, 40, 50..... Then the data will be shown correctly.
0 comment threads
1 comment thread