MySQL LIMIT 1 but query 15 rows?
- by Ian
Basically what I'm trying to do is compare the ID's of rows against 15 results in MySQL, eliminating all but 1 (using NOT IN) and then pull that result.
Now normally this would be fine by itself, however the order of the 15 rows I'm doing the SQL query for are constantly changing based on a ranking, so there is a possibility that between the time the ranking updates, and the ajax request (which I submit the ID's for NOT IN) more than just one ID has changed, which would of course bring back more than one row which I do not want.
So in short, is there a way in which I can query 15 rows, but only return one? Without having to run two separate queries. Any help is appreciated, thank you.
EXAMPLE:
Say I have 7 items in my database, and I'm displaying 5 on the page to the user.
These are what are being displayed to the user:
Apple
Orange
Kiwi
Banana
Grape
But in the database I also have
Peach
Blackberry
Now what I want to do is if the user deletes an item from their list, it will add another item (based on a ranking they have)
Now the issue is, in order to know what they have on their list at the moment I send the remaining items to the database (say they deleted Kiwi, I would send Apple, Orange, Banana, and Grape)
So now I select the highest ranked 5 items from are remaining six items, make sure they are not the ones already displayed on the page, and then add the new one to list (either Peach or Blackberry)
All good and well, except that if both peach and blackberry now outrank grape, then I will be returning two results instead of just one. Because it would've searched...
Apple
Orange
Banana
Peach
Blackberry
and excluded...
Apple
Orange
Banana
Grape
Which leaves us with both Peach and Blackberry, instead of just Peach or Blackberry
