generationgav
asked on
Mysql - select continuous ID's
Hi,
I want to do a query such as the following;
SELECT * FROM Bookings WHERE Status=0 LIMIT 3
But I want the results to be continuous in the database, so the following;
Id Status SeatNo
1 0 1
2 1 2
3 1 3
4 0 4
5 0 5
6 0 6
Will bring back 4, 5 and 6 And not 1, 4, 5 as it currently does.
Is there any optimal way of doing this rather than doing the query then checking if it's ok and if not then doing a reselect from the 2nd ID
I want to do a query such as the following;
SELECT * FROM Bookings WHERE Status=0 LIMIT 3
But I want the results to be continuous in the database, so the following;
Id Status SeatNo
1 0 1
2 1 2
3 1 3
4 0 4
5 0 5
6 0 6
Will bring back 4, 5 and 6 And not 1, 4, 5 as it currently does.
Is there any optimal way of doing this rather than doing the query then checking if it's ok and if not then doing a reselect from the 2nd ID
Alex
if you want all the records that has their status as 0 why you set a LIMIT?Remove the limit=3 and then you will take all the records you want.
ASKER
I require a limit, as there is going to be millions of records.
I want people to be able to select between 1 and 15 records; but they need to be continuous. Once they're selected they could be freed again (as people can cancel their bookings) but if there's a slot of 3 freed and then somebody wishes to book 5, I want it to ignore the slot of 3 and carry on looking.
I want people to be able to select between 1 and 15 records; but they need to be continuous. Once they're selected they could be freed again (as people can cancel their bookings) but if there's a slot of 3 freed and then somebody wishes to book 5, I want it to ignore the slot of 3 and carry on looking.
so you can set a limit to present your records and make a pagination so you will be able to present some pages and 3 records or more in each page.
ASKER
The issue is, in a normal select you would get "1,4,5" But I want the ID's to be CONTINUOUS so if you did LIMIT 1 it'd bring back "1" but with LIMIT 3 it would bring back "4,5,6"
I think this could be done inside SQL, but the query would be slow, cumbersome, and not easily extensible. Perhaps the solution lies in coding a stored procedure. The intricate logic certainly points to code vs. SQL, but the amount of potential return makes any outside manipulation a slow process.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
did you have any "ORDER BY ID ASC" inside your sql statement?
ASKER
Sorry, I had forgotten my ORDER BY, but meant to be there.
Thank you bportloc; unfortunately not the answer I was hoping for, but certainly the one I was expecting, and slightly better than my solution too!
I was going to compare one row with the next, and if they were continuous repeat until I found 3 which were continuous.
Thank you bportloc; unfortunately not the answer I was hoping for, but certainly the one I was expecting, and slightly better than my solution too!
I was going to compare one row with the next, and if they were continuous repeat until I found 3 which were continuous.