Menu
I have the following scenario, I have a table column with the name categoryid in table categories, I need to select all rows within the table where categoryid is 100 and above in intervals of 100.
eg
SELECT * FROM categories WHERE categoryid IN(100,200,300,400,500,600,700,800,900,1000,1100,1200,1300,1400,1500,1600,1700,1800,1900,2000,2100,2200,2300,2400,2500,2600,2700,2800,2900,3000...
Is there a better way to do this than manually typing all intervals, it should start at hundred and go up to the last number. Keep in mind that the last number can change as client adds new categories. is this possible, hope I make sense.
244
For the above logic, you can use BETWEEN. BETWEEN clause is inclusive, for example, suppose there are 1,2,3,4,5,6 numbers. If you want to display numbers from 2 to 6 inclusively, then using BETWEEN the numbers 2 and 6 will also get displayed.
To generate a range of numbers in MySQL, you can use stored procedure. Firstly, we need to create a table. After that, we will create a stored procedure that generates a range of number from 10 to 1. After that we need to call the stored procedure that fills a range of numbers in the table.
The range access method uses a single index to retrieve a subset of table rows that are contained within one or several index value intervals. It can be used for a single-part or multiple-part index.
MySQL select rows by range using ROW_NUMBER() function The ROW_NUMBER() is a window function in MySQL that assigns a sequential number to each row. Hence we can use the row_number() function to select rows in a particular range.
So this % operator that I have used is called the modulo operator and it will give you the remainder after dividing by a number (in this case 100). If the remainder is 0 then it's a multiple of 100. We also need the >= 100 condition because zero also passes the modulo condition.
SELECT * FROM categories WHERE categoryid >= 100 AND categoryid % 100 = 0;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
