asked on # SQL create line numbers for data sampling

I have approximately 4700 shipments where I need to grab a sampling based on every 8 the shipment. What I'd like to do is generate a line number when I select the 4700 shipments but start from one after each 8th occurrence.

i.e.

Line Shipment #

1 Shipment 1

2 Shipment 2

3 Shipment 3

4 Shipment 4

5 Shipment 5

6 Shipment 6

7 Shipment 7

8 Shipment 8

1 Shipment 9

2 Shipment 10

3 Shipment 11

………

Thanks

i.e.

Line Shipment #

1 Shipment 1

2 Shipment 2

3 Shipment 3

4 Shipment 4

5 Shipment 5

6 Shipment 6

7 Shipment 7

8 Shipment 8

1 Shipment 9

2 Shipment 10

3 Shipment 11

………

Thanks

Microsoft SQL Server

Great!

This selects every 8th occurrence, however what would I need to change in order to see all the 4700 shipments and their line numbers?

i.e.

Line Shipment #

1 Shipment 1

2 Shipment 2

3 Shipment 3

4 Shipment 4

5 Shipment 5

6 Shipment 6

7 Shipment 7

8 Shipment 8

1 Shipment 9

2 Shipment 10

3 Shipment 11

This selects every 8th occurrence, however what would I need to change in order to see all the 4700 shipments and their line numbers?

i.e.

Line Shipment #

1 Shipment 1

2 Shipment 2

3 Shipment 3

4 Shipment 4

5 Shipment 5

6 Shipment 6

7 Shipment 7

8 Shipment 8

1 Shipment 9

2 Shipment 10

3 Shipment 11

```
;with cte as (
select *, row_number() over (order by shipmentID) rn
from table)
select *, case when rn%8 =0 then 8 else rn%8 end from cte
```

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Works great as long as the group number is 8, if I change to another grouping number from 8 to 6, it does not give me every 6th item.

what exactly are you trying to do? Are you looking for every Nth item or starting the line numbers over?

While initially I was looking for the line numbers to start over, your first gave me the Nth item which was great as well. Your gave me the line numbers and starting over. Best of both worlds.

I assumed that all I needed to do is change the group by number and I would get the results I needed. While the query for starting over works regardless of the group number passed, the original query giving me the Nth item only seems to work with the group number of 8.

I assumed that all I needed to do is change the group by number and I would get the results I needed. While the query for starting over works regardless of the group number passed, the original query giving me the Nth item only seems to work with the group number of 8.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

It's all based on the mod function.

start with this:

what that does is just simply puts a row number (rn) on each line. 1...4700

From there:

so 10%8 = 2 (2 left over) and 8%8 = 0 (divides evenly).

so if you want every 8th row:

But no point in doing a rowReset at that point cause it'll always be the 8th row. Unless you want to do new row_numbers on every 8th row?

start with this:

```
;with cte as (
select *, row_number() over (order by shipmentID) rn
from table)
Select * from CTE
```

what that does is just simply puts a row number (rn) on each line. 1...4700

From there:

```
;with cte as (
select *, row_number() over (order by shipmentID) rn
from table)
Select *, case when rn%8 = 0 then 8 else rn%8 end as rowReset from CTE
```

this code will reset the rownumber. You'll have RN as the original row number and rowReset as the new one. The modulus function returns the remainder of the divisor. so 10%8 = 2 (2 left over) and 8%8 = 0 (divides evenly).

so if you want every 8th row:

```
;with cte as (
select *, row_number() over (order by shipmentID) rn
from table)
Select * from CTE
where rn%8 = 0
```

But no point in doing a rowReset at that point cause it'll always be the 8th row. Unless you want to do new row_numbers on every 8th row?

I thought it might be easier for me to show you what I get.

The attached spreadsheet has two tabs, Using 8, Using 6.

Each tab has the code I'm using and part of the results

ShipGroup.xlsx

The attached spreadsheet has two tabs, Using 8, Using 6.

Each tab has the code I'm using and part of the results

if you take out the where rn % 6 = 0 do you get row_numbers for every line?

you might want to do the join on the CTE rather than the inner table.

you might want to do the join on the CTE rather than the inner table.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

If I take out the where rn % 6 = 0 I do get Row Numbers for every line 1-4700

That's odd that 8 works but not 6.

The results of the mod should work regardless of number used

That's odd that 8 works but not 6.

The results of the mod should work regardless of number used

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Open in new window