Friday, March 9, 2012

Join to select a 'weighted' column

Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.

declare @.inv table (item varchar(50), supplierid int)

declare @.supplier table (supplierid int, weight int)

set nocount on

insert into @.inv values ('item1', 1)

insert into @.inv values ('item1', 2)

insert into @.inv values ('item2', 2)

insert into @.inv values ('item2', 3)

insert into @.supplier values(1, 30)

insert into @.supplier values(2, 20)

insert into @.supplier values(3, 10)

-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3

select item, ps2.supplierid from @.supplier ps2 join

(select item, min(ps.weight)'weight'

from @.inv inv join @.supplier ps on inv.supplierid=ps.supplierid

group by item) iw on ps2.weight=iw.weight

Is there a better alternative to this?

Thanks in advance,

Mike

You can do the following in SQL Server 2005:

select item, supplierid
from (
select i.item, s.supplierid, row_number() over(partition by i.item order by s.weight) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
) as si
where wt = 1

But the most efficient way is to do below:

select item, cast(substring(wt, 5, 4) as int) as supplierid
from (
select i.item, min(cast(s.weight as binary(4)) + cast(s.supplierid as binary(4))) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
group by i.item
) as si

The second method will work only if the weight/supplierid values are greater than or equal to zero due to the conversion to binary. You can make it work for negative values also by modifying the expression. The trick is to get a sortable value using a combination of weight/supplierid that you can apply the aggregate function on and then get the individual values out.

|||

Thank you very much,

I will look at both.

Mike

No comments:

Post a Comment