CODEWITHSUNDEEP
sqlsql-serverselect
rohanharrison
rohanharrison

Reputation: 233

How can I select a column from my table, and get very nth row based on the value in that column in SQL Server?

I have a table called DIVISION filled with numeric values. They are not unique. There is a person as reach record and they have a DIVISION assigned to them. I want to be able to select every nth row in my table based on the DIVISION.

So say if DIVISION 211 has 60k records and we select every nth row and get 13k records. Then we move on to say 222 which has 100k records and we select 23k.

Also, I need to be able to start counting each nth row after the first 13. So basically, go to 13 and use that as the starting point. After 13 select every nth row.

I honestly have no idea where to even begin with this, I tried a bunch of examples from Google and most of the time I couldn't even get the syntax correct enough to run.

I found something for MySQL that seems like it could somewhat do what I want, but I couldn't figure out the SQL Server equivalent of this.

SELECT *
FROM 
    (SELECT
        @row := @row +1 AS rownum, noun
     FROM 
         (SELECT @row :=0) r, nouns) ranked
WHERE rownum %4 =1

Upvotes: 1

Views: 60

Answers (1)

shawnt00
shawnt00

Reputation: 17953

This assumes you have a late enough version of SQL Server to use row_number(). It's very likely you do:

with data as (
    select *, row_number() over (partition by DIVISION order by <sort by column>) as rn
    from WORK
)
select *
from data
where rn <= 13 or (rn - 13) % 18 = 1;

Upvotes: 1

Related Questions