CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Haminteu
Haminteu

Reputation: 1334

Select every ten steps SQL

I have the following table:

----------------------------------------------
oNumber      oValue1
----------------------------------------------
1            54
2            44
3            89
4            65
ff.
10           33
11           22
ff.          
20           43
21           76
ff.          
100          45

I want to select every 10 value in oNumber. So the result should be:

----------------------------------------------
oNumber      oValue1
----------------------------------------------
10           33
20           43
ff.
100          45

Also, oNumber is not a sequence number. It's just a value. Even it isn't a sequence number, 10, 20, 30 and so on will always appear under oNumber field.

Does anyone know how is the tsql for this case?
Thank you.

Upvotes: 3

Views: 192

Answers (3)

square_particle
square_particle

Reputation: 524

In the case you simply want multiples of 10, then just use the modulo operator as stated by Daniel and Ian.

select *
from table
where oNumber % 10 = 0;

However, I felt that you could be alluding to the fact that you want to get every 10th item in your list. If that's the case, which it may be not, you would simply just sequence your set based on oNumber and use the modulo operator.

select *
from (
    select *,
        RowNum = row_number() over (order by oNumber)
    from table) a
where RowNum % 10 = 0;

Upvotes: 2

user5359735
user5359735

Reputation:

Use the "Modulo" operator - %. So in this case, the answer would be something like: 

SELECT * FROM table WHERE oNumber % 10 = 0

This will only load if oNumber is a number divisible by ten (and therefore has a remainder zero).

Upvotes: 5

Daniel A. Thompson
Daniel A. Thompson

Reputation: 1924

select * from table where oNumber % 10 = 0

https://msdn.microsoft.com/en-us/library/ms190279.aspx

Upvotes: 8

Related Questions