SELECT rows that are a multiple of x

Question

In SQL Server, how do I select rows 10, 20, 30, 40, etc where the RowID is an equal gap of some integer (+10). There are 50k rows, so using IN (1,10,20,etc) is laborious.

SELECT * FROM 'TABLENAME' WHERE RowID = 10 (+ 10)

Solution

You can use modulo for that.

SELECT * FROM `table` WHERE (`id` % 10) = 0

SELECT * FROM `table` WHERE (`id` MOD 10) = 0

SELECT * FROM `table` WHERE !MOD(`id`, 10)

Anyone should do.

Comments

[Daniel Comp]

Thanks, I've learned that Mod stands for modulo, which represents the remainder of an integer division. However, if you do 12 MOD 10, the result is 2 and if you do 3 MOD 5, the result is 0, and !MOD isn't recognized by SQL. % is the correct symbol, but not the proper operator for my need. Thanks for trying.

[Daniel Comp]

This results in something closer than my guess, but if I want every 100th row, I'm using ModuleID % .01 = 0. The dividend % divisor doesn't make sense to me for what I need - every 10th row starting with row 500 for example

[EvilTeach]

add a where row > 499

[EvilTeach]

generally it should be id mod 100, not .01

[Havenard]

You are wrong. 3 MOD 5 is 3. Its aways the remainder of the division, therefore when its a perfect division it will be zero. n MOD 10 will be 0 for 10, 20, 30, 40, 50 etc. You can use this information to identify precisely which rows you want.

[Daniel Comp]

This was my scotoma: tizag.com/sqlTutorial/sqloperators.php Modulus performs division, dividing the first digit by the second digit, but instead of returning a quotient, a "remainder" value is returned instead. The WHERE CLAUSE that works in MSSQL (for me) WHERE (PortalID % 10 = 0) This results in every 10th id selected WHERE (PortalID % 50 = 0) This results in every 50th id selected