CODEWITHSUNDEEP
phpmysql
Elitmiar
Elitmiar

Reputation: 36839

MySql - How to select a range of id's at set intervals

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.

Upvotes: 6

Views: 8856

Answers (5)

Galz
Galz

Reputation: 6832

use the Modulo operator which is depicted with %

SELECT * FROM categories 
WHERE categoryid >= 100 
AND categoryid % 100 = 0;

Upvotes: 2

gnur
gnur

Reputation: 4733

Just to give a different option, you can also do this with LIKE

SELECT * FROM categories WHERE categoryid LIKE "%00";

Upvotes: 1

aiham
aiham

Reputation: 3614

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;

Upvotes: 13

Morten
Morten

Reputation: 3854

Use the function in the where clause:

SELECT xField FROM yTable WHERE ({ fn MOD(JourneyPatternPointId, 100) } = 0)

Upvotes: 0

Erik
Erik

Reputation: 91270

Use modulo (remainder of division).

WHERE (MOD(categoryid, 100) = 0) AND (categoryid >= 100)

Upvotes: 2

Related Questions