Advertisement:

Author Topic: SQL question  (Read 446 times)

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,241
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
    • Study Force
SQL question
« on: June 11, 2018, 12:18:25 PM »
Hi everyone,

Say I have a table with many rows, and I wanted to limit the query between rows 10,000 and 20,000 or 300 to 400, how would I change the
Code: [Select]
LIMIT part to reflect that?

Online vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,139
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: SQL question
« Reply #1 on: June 11, 2018, 01:03:51 PM »
LIMIT 10000, 20000
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 284
Re: SQL question
« Reply #2 on: June 11, 2018, 01:49:03 PM »
The fastest and litte complex way is to use Keyset Pagination:
https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,241
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
    • Study Force
Re: SQL question
« Reply #3 on: June 11, 2018, 04:16:09 PM »
Thank you both.

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,241
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
    • Study Force
Re: SQL question
« Reply #4 on: June 13, 2018, 12:23:57 AM »
LIMIT 10000, 20000


Are you sure this will give me 10000 rows? I.e. 20,000 - 10,000 = 10,000?

Please when I write $count = mysql_num_rows($request); it gives me 20,000. I need the difference

Online vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,139
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: SQL question
« Reply #5 on: June 13, 2018, 12:25:31 AM »
No it doesn't it should be
LIMIT 10000, 10000

Since the first returns 20k records

Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,241
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
    • Study Force
Re: SQL question
« Reply #6 on: June 13, 2018, 09:01:34 AM »
Maybe I'm not communicating what I want very well.

I'll try again. Pretend I want rows between 30 and 40. In other words, I want 10 row output between row 30 and 40, would I use

LIMIT 30, 40? I tried that already and it didn't work.

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 56,275
  • Gender: Male
    • Kindred-999 on GitHub
Re: SQL question
« Reply #7 on: June 13, 2018, 09:26:37 AM »
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Online vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,139
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: SQL question
« Reply #8 on: June 13, 2018, 09:28:17 AM »
Normally I use id's to return the rows i want rather than limit offsets. Limits i generally use with paging

Such as where id_msg >= 30 and id_msg <= 40
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,241
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
    • Study Force
Re: SQL question
« Reply #9 on: June 13, 2018, 10:23:00 AM »
Normally I use id's to return the rows i want rather than limit offsets. Limits i generally use with paging

Such as where id_msg >= 30 and id_msg <= 40


This works best for my needs, thanks for the idea.

Online vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,139
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: SQL question
« Reply #10 on: June 13, 2018, 11:04:23 AM »
That is always the most efficient way as well.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro