number rows by group

Question

number rows by group

collie

Old Hand

Points: 367
More actions
February 7, 2010 at 2:43 am

#215391

I haver a query that i need to add row numbers by group.
Can someone please help?
This is the sql i have:
if object_id('tempdb..#TempWinShuttle') is not null
drop table #TempWinShuttle
create table #TempWinShuttle (row int ,id nchar (10),orderid nvarchar (100),material nvarchar (50)
, billto_id nvarchar (100),shipto_id nvarchar (50),shipped_quantity int,materialhidden nvarchar (50)
,Orderhidden nvarchar (50))
insert into #TempWinShuttle
select DISTINCT 1 row
,'' as Material,shipto.billto_id as billto_id,shipto.shipto_id shipto_id
,'' AS shipped_quantity,'' as materialhidden
,packlist.order_id as Orderhidden
from packlist
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join carton c on c.packlist_id=packlist.packlist_id
inner join (select sum(cartonitem_quantity) cartonitem_quantity
,cartonitem_style_code+'-'+cartonitem_color_code as material,packlist.order_id
from cartonitem inner join carton on carton.carton_id=cartonitem.carton_id
inner join packlist on packlist.packlist_id=carton.packlist_id
and packlist.order_id is not null
group by cartonitem_style_code+'-'+cartonitem_color_code ,packlist.order_id) cartonitem
on cartonitem.order_id=packlist.order_idwhere packlist_status=1
AND packlist.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleARPReport)
and packlist.order_id in('1035','4119')
insert into #TempWinShuttle
select DISTINCT 2 row
,'D',packlist.order_id as order_id,material AS Material
,'' as billto_id,'' shipto_id
,'' AS shipped_quantity
,material as materialhidden,packlist.order_id as Orderhiddenfrom packlist
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join carton c on c.packlist_id=packlist.packlist_id
inner join (select sum(cartonitem_quantity) cartonitem_quantity
,cartonitem_style_code+'-'+cartonitem_color_code as material
,packlist.order_idfrom cartonitem
inner join carton on carton.carton_id=cartonitem.carton_id
inner join packlist on packlist.packlist_id=carton.packlist_id
and packlist.order_id is not null
group by cartonitem_style_code+'-'+cartonitem_color_code ,packlist.order_id) cartonitem
on cartonitem.order_id=packlist.order_id
where packlist_status=1
AND packlist.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleARPReport)
and packlist.order_id in('1035','4119')
insert into #TempWinShuttle
select DISTINCT 3 row
,'D1',packlist.order_id as order_id
,'' AS Material,'' as billto_id
,'' shipto_id ,cartonitem_quantity AS shipped_quantity,material as materialhidden
,packlist.order_id as Orderhiddenfrom packlist
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join carton c on c.packlist_id=packlist.packlist_id
inner join (select sum(cartonitem_quantity)
cartonitem_quantity,cartonitem_style_code+'-'+cartonitem_color_code
as material,packlist.order_idfrom cartonitem
inner join carton on carton.carton_id=cartonitem.carton_id
inner join packlist on packlist.packlist_id=carton.packlist_idand packlist.order_id is not null
group by cartonitem_style_code+'-'+cartonitem_color_code ,packlist.order_id) cartonitem on
cartonitem.order_id=packlist.order_id
where packlist_status=1
AND packlist.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleARPReport)
and packlist.order_id in('1035','4119')
select distinct row as oldrow,id,orderid+ '- ARP' AS orderid
,shipto_id,billto_id,material,materialhidden,shipped_quantity
,Row_number()Over(Partition by orderhidden,id order by row) as newrow
from #TempWinShuttleorder
by oldrow,orderid,newrow
The results are desired results are in the screenshot in the link:

How can i fix my sql to get the desired results?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Use a column list for each of your three inserts into the temp table so that you know for sure that your query output columns are going into the correct columns in the temp table. Here's an example for the first of the three queries - check that it's correct, then do the same for the other two.

This won't answer your posted question but it may make your results more consistent with your expectations.

-- First INSERT

insert into #TempWinShuttle (row, Material, billto_id, shipto_id, shipped_quantity, materialhidden, Orderhidden)

select DISTINCT 1 row

,'' as Material

,s.billto_id as billto_id

,s.shipto_id shipto_id

,'' AS shipped_quantity,

'' as materialhidden

,p.order_id as Orderhidden

from packlist p

inner join shipto s on s.shipto_id = p.shipto_id

inner join carton c on c.packlist_id = p.packlist_id

inner join (

select sum(cartonitem_quantity) cartonitem_quantity

,cartonitem_style_code+'-'+cartonitem_color_code as material

,packlist.order_id

from cartonitem

inner join carton on carton.carton_id = cartonitem.carton_id

inner join packlist on packlist.packlist_id = carton.packlist_id and packlist.order_id is not null

group by cartonitem_style_code+'-'+cartonitem_color_code

,packlist.order_id

) cartonitem on cartonitem.order_id = p.order_id

WHERE packlist_status = 1

AND p.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleARPReport)

and p.order_id in ('1035','4119')

Cheers

ChrisM@home

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

It's hard to tell from that what rows you're removing. What is the criteria on which you want to build results? For each ID, I need all the ORDERIDs, or just the first one, the sum of quantity, etc.

I think you need to sum things somewhere, but it's not clear what you're looking for.