Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

Why OFFSET isn't working the way it is expected?

+1
−2

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```
Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

1 comment thread

The guide is fine (1 comment)

1 answer

+1
−0

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.

Why does this post require moderator attention?
You might want to add some details to your flag.

0 comment threads

Sign up to answer this question »