To Get fixed periodic rownumbers by rounding the number to closest integer

Question

To Get fixed periodic rownumbers by rounding the number to closest integer

harishyarlagadda2020

Valued Member

Points: 62
More actions
December 28, 2015 at 11:51 am

#314478

I am giving example of what I am looking for below: Any help is appreciated
we have 43000 records and we have check that can handle 5000 records for hit rate, so I was requested to pull exactly 5000 records : pick a record every 8.6 records(43000/5000) , so pick up the first record, then the 10th record (round(1+8.6)=10), then the 18th record (round(1+2*8.6)=round(18.2)=18), etc.
Note:
I tried getting 5000 random numbers using newid() which is giving random 5000 but not the 1,10,18,... so on which is not what I was requested to pull.
Also I have tried rownum and filtered by row % 9 = 0 which is getting nearly 4800 records.
I am looking for the way to round to nearest integer and get 5000 records as explained in example.
Any help is appreciated.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 1

This is straight forward, select from the table where (Row_Number % Divisor) is less than one, rounding will not give the right results because of the loss of precicion.

😎

Quick sample

;WITH SAMPLE_43000 AS

(

SELECT TOP (43000)

ROW_NUMBER() OVER

(

ORDER BY (SELECT NULL)

) AS N

FROM sys.all_columns SAC1

CROSS JOIN sys.all_columns SAC2

)

SELECT

COUNT(S43K.N) AS R_COUNT

FROM SAMPLE_43000 S43K

WHERE ( S43K.N % 8.6 ) < 1;

Results

R_COUNT

5000

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Eirikur Eiriksson (12/28/2015)
This is straight forward, select from the table where (Row_Number % Divisor) is less than one, rounding will not give the right results because of the loss of precicion.
😎
Quick sample
;WITH SAMPLE_43000 AS
(
SELECT TOP (43000)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS N
FROM sys.all_columns SAC1
CROSS JOIN sys.all_columns SAC2
)
SELECT
COUNT(S43K.N) AS R_COUNT
FROM SAMPLE_43000 S43K
WHERE ( S43K.N % 8.6 ) < 1;
Results
R_COUNT
5000

Nice job, Eirikur. I was working on the same approach, but was shooting for = 0 instead of < 1. The ceiling of the decimal division was starting to mess with my head. Your way is better.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

harishyarlagadda2020 Valued Member Points: 62 More actions · Answer 3

Thank you Eirikur! I am using rownum% value= 0 rather than '<1'.

This helped.

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 4

Ed Wagner (12/28/2015)
Nice job, Eirikur. I was working on the same approach, but was shooting for = 0 instead of < 1. The ceiling of the decimal division was starting to mess with my head. Your way is better.

Thanks Ed

If you are aiming for = 0 then that would translate to FLOOR(ROW_NUMBER % DIVISOR) = 0, (ROW_NUMBER % DIVISOR) < 1 does the same with one less operator in the execution plan;-)

😎

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 5

harishyarlagadda2020 (12/28/2015)
Thank you Eirikur! I am using rownum% value= 0 rather than '<1'.
This helped.

You are very welcome.

😎