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