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
jdr0606Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
A CTE with row_number() should do nicely for you:

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

select * from cte
where 
rn % 8 = 0;

Open in new window

0
jdr0606Author Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
;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

Open in new window

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

jdr0606Author Commented:
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.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
what exactly are you trying to do?  Are you looking for every Nth item or starting the line numbers over?
0
jdr0606Author Commented:
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.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
It's all based on the mod function.

start with this:

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

Open in new window


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

Open in new window

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

Open in new window


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?
0
jdr0606Author Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
jdr0606Author Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
try inserting it into a temporary table first:

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


select * from #temp where rn % 6 = 0

I know it shouldn't effectively change the results, just making sure you get the rows.  Wonder if there is something other optimization going on.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.