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
Community Proposals
Community Proposals
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

Welcome to Software Development on Codidact!

Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.

Post History

42%
+1 −2
Q&A 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'...

1 answer  ·  posted 1y ago by billy‭  ·  edited 1y ago by Alexei‭

Question mysql sql
#3: Post edited by user avatar Alexei‭ · 2022-11-03T18:36:03Z (over 1 year ago)
added relevant tag
#2: Post edited by user avatar billy‭ · 2022-11-03T15:09:37Z (over 1 year ago)
  • 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).
  • ```SQL
  • 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](https://www.sqltutorial.org/sql-limit/) I followed, [W3SCHOOLS](https://www.w3schools.com/php/php_mysql_select_limit.asp) 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 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).
  • ```SQL
  • 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](https://www.sqltutorial.org/sql-limit/) I followed, [W3SCHOOLS](https://www.w3schools.com/php/php_mysql_select_limit.asp) 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: Initial revision by user avatar billy‭ · 2022-11-03T15:04:12Z (over 1 year ago)
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). 

```SQL
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](https://www.sqltutorial.org/sql-limit/) I followed, [W3SCHOOLS](https://www.w3schools.com/php/php_mysql_select_limit.asp) 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)

sql