asked on

sql join/ assign small # first

Hello,
I have 2 tables (examples below). I need to match up the orderid and sku and then assign the seq from the first table to the second one. The lowest trackid should get the lowest seq. So 642210341 should get 84. There can be several of the same sku , but the sku will always be unique as will the trackid be. Is there a way to accomplish this without looping through all the records?

SKU        OrderID        Drops       Seq
00000004912348      009764341      1        84
00000004912348      009764341      1        85
00000004912571      009764341      1        129
00000004912571      009764341      1        130

trackid        sku        orderid      Seq?
642209934      00000004912348      009764341
642210341      00000004912348      009764341
642146053      00000004912571      009764341
642146100      00000004912571      009764341

Éric Moreau

could it be:

select t2.orderid, t2.sku, t1.seq
from (
   select orderid, sku, min(trackid) as trackid
   from table2
   group by orderid, sku
) as t2
inner join (
  select orderid, sku, min(seq) as seq
  from table1
  group by orderid, sku
) as t1
on t1.orderid = t2.orderid
and t1.sku = t2.sku

Open in new window

Éric Moreau

and if you want to assign it:

update table2
set Seq = t1.seq
from table2 as table2
inner join (
   select orderid, sku, min(trackid) as trackid
   from table2
   group by orderid, sku
) as t2
on t2.orderid = table2.orderid
and t2.sku = table2.sku
inner join (
  select orderid, sku, min(seq) as seq
  from table1
  group by orderid, sku
) as t1
on t1.orderid = t2.orderid
and t1.sku = t2.sku

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

>>The lowest trackid should get the lowest seq. So 642210341 should get 84.<<
But 642210341 is not the lowest trackid, 642146053 is. Am I missing something?

awking00

Perhaps you want the lowest trackid per sku group -
select b.trackid, a.sku, a.orderid, a.seq
from
(select sku, orderid, seq, row_number() over (partition by sku order by seq) rn
from table1) a,
(select sku, orderid, trackid, row_number() over (partition by sku order by trackid) rn
from table2) b
where a.sku = b.sku
and a.rn = b.rn;

coperations07

ASKER

Thanks for all the replies!

I think I may not have stated my need quite clearly enough... The lowest trackid per sku group should get the lowest seq. Then the second lowest trackid for the sku group should get the second lowest seq. etc...

I realized what was going on there after I plugged in Eric's suggestion and all of the like sku's had the same low seq #.

Pawan Kumar

try this. If below is not what you need, then post the expected output.

CREATE TABLE t1 
(
	 SKU VARCHAR(50)            
	,OrderID VARCHAR(50)           
	,Drops TINYINT        
	,Seq SMALLINT
)
GO

INSERT INTO t1 VALUES
('00000004912348',      '009764341' ,     1 ,          84),    
('00000004912348',      '009764341' ,     1  ,         85),   
('00000004912571',      '009764341'  ,    1  ,        129),   
('00000004912571',      '009764341'  ,    1   ,       130)
GO

CREATE TABLE t2
(
	 trackid BIGINT             
	,SKU VARCHAR(50)                                 
	,OrderID VARCHAR(50)     
)
GO

INSERT INTO t2 VALUES
(642209934,      '00000004912348',      '009764341'),      
(642210341,      '00000004912348',      '009764341'),      
(642146053,      '00000004912571',      '009764341'),     
(642146100,      '00000004912571',      '009764341')
GO

SELECT l.OrderID,l.SKU,l.trackid,u.Seq FROM
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) r1 FROM t1
)u
INNER JOIN
(	
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) r1 FROM t2
)l ON l.OrderID = u.OrderID AND u.SKU = l.SKU AND u.r1 = l.r1

Open in new window

awking00

>>The lowest trackid per sku group should get the lowest seq. Then the second lowest trackid for the sku group should get the second lowest seq. etc<<
Isn't that what my query produces (and also the same as Pawan Kumar Khowal's)?

coperations07

ASKER

Yes it looks like you are right awking00. Those 2 queries do produce the correct results for a small sample size. I have a large amount of data to run it against. I have Pawan's running currently and it has taken 40 minutes to get through 18,000 records. The data set could be up to 100,000 records. Is there a faster alternative?

Pawan Kumar

Hi coperations07,
Any update on this?

Regards,
Pawan