1

I am new to here and please let me know if I didn't make the question clearly.

If I have a m rows in a table and I want to select n rows uniformly. In other words, the interval between first row and next row is m / n.

For example, if i have a 10 rows and I want to select 5 rows out of them, then the selected rows would be 1, 3, 5, 7, 9 because the interval is 10 / 5 = 2.

Does anyone can help?

  • Which RDBMS are you using? There are a number of ways of doing this – Philᵀᴹ Apr 22 '16 at 18:40
  • I am using SQL server management studio – Matthew Apr 22 '16 at 18:42
3

You can use row_number and the modulo operator

Tables don't have any inherent order. You need to define an ordering expression (e.g column names) to define what "first" and "next" mean.

DECLARE @StepSize int = 2;

WITH CTE AS
(
SELECT *,
       ROW_NUMBER() OVER (ORDER BY YourOrderingColumn) AS RN
FROM YourTable
)
SELECT *
FROM CTE
WHERE RN % @StepSize = 1;
  • thanks but i am not selecting the odd number rows. if the step size is 3 then I will select 1, 4, 7 etc. – Matthew Apr 22 '16 at 20:19
  • So change %2 to %3 in that case. – Martin Smith Apr 22 '16 at 20:20
  • one more question – Matthew Apr 22 '16 at 20:35
  • why declare @tot = select count(*) from table; does not work? I want to calculate the total number of rows in the table – Matthew Apr 22 '16 at 20:36
  • Because it's not valid syntax. declare @tot int = ( select count(*) from table); is correct. – Martin Smith Apr 22 '16 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.