CODEWITHSUNDEEP
mysqlselectrangelimit
Julie Rokk
Julie Rokk

Reputation: 560

MYSQL - LIMIT inside a range

I have built a slider using AJAX for the results, and i have a little problem as follow:

assuming a table with a set of 1 to 15 rows, but, i'm working only with the first 9 rows; in a 1st stage i (SELECT * FROM table ORDER BY id DESC LIMIT 9) and use the results to build navigation and for displaying the first group.

Then in a 2nd stage i'm querying theese 9 items, 3 each time from left so i (SELECT * FROM table WHERE id > '$1' ORDER BY id DESC LIMIT 3) or right so i (SELECT * FROM table WHERE id < '$1' ORDER BY id DESC LIMIT 3) where $1 is the last item of each group so (13 || 11 || 7) from (15, 14, 13) || (12, 11, 10) || (9, 8, 7).

Well, immagine to have a representation of above verbose explanation like below:

<- [ 15 - 14 - 13 - 12 - 11 - 10 - 9 - 8 - 7 ] ->
   [    15     ]  [    14     ]  [    13     ]

Ok, the problem is when the "right-query" reach the last item: 7, the results-set is (6, 5, 4) that obviously is correct but isn't in our range of 15 to 7, instead for my purpose it should return nothing. how i can do this with one SQL query?

just for sake here is the slider demo ( latest item to the right, using the right arrow, is out of range ); hope it is not too fuzzy question cause i had an hard time figuring out how to explain the problem. ;)

Upvotes: 1

Views: 1155

Answers (1)

Dan Simon
Dan Simon

Reputation: 13137

In the 1st stage, you can record the minimum id in the returned rows. Then in your other queries, just add a WHERE condition to make sure the selected ids are greater than or equal to that minimum, for example

SELECT * FROM table WHERE id <'$1' AND id >='$min' ORDER BY id DESC LIMIT 3

in the case where your minimum id is 7 and the "right" query is done with the value 7, it will return nothing. If I understand your question correctly, this is the behavior you want.

Upvotes: 1

Related Questions