Hi there all,

I have one I simply cannot get my head around, am I being daft or is it staring me in the face?

I'll start with the data:

tbl_1
sku rank price
abc 1 10.99
abc 2 11.99
abc 3 12.99
abc 3 13.99
xyz 1 10.99
xyz 2 11.99
xyz 3 12.99
xyz 3 13.99

tbl_2
sku [price] [min-price]
abc 11.50 x.xx
xyz 12.50 x.xx

I need to pick out the price from tbl_1 where it is greater than the price in tbl_2, but has the lowest rank and update that value into tbl_2 and of course has the same sku (tbl_1.sku = tbl_2.sku).

Nothing I try will seem to allow me to do it, I can push it into a new table with an INSERT query but, is there any reason why I cannot do this with an UPDATE?

Any help would be greatfully received.

Thanks so much in advance for your asistance.

Stuart

tbl_1
sku rank price
abc 1 10.99
abc 2 11.99
abc 3 12.99
abc 4 13.99
xyz 1 10.99
xyz 2 11.99
xyz 3 12.99
xyz 4 13.99

tbl_2
sku [price] [min-price]
abc 11.50 x.xx
xyz 12.50 x.xx

This query will give you the lowest rank for each sku where the price in tbl_1 is greater than the price in tbl_2

SELECT sku, MIN(Rank) AS Rank
FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.sku = tbl_2.sku AND
tbl_1.price >= tbl_2.price

Now to get the actual tbl_1 record, you need to join this back to tbl_1:

SELECT tbl_1.*
FROM tbl_1 INNER JOIN (SELECT sku, MIN(Rank) AS Rank
FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.sku = tbl_2.sku AND
tbl_1.price >= tbl_2.price) A
ON tbl_1.sku = A.sku and tbl_1.rank = A.rank

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

Thank you for your fast response..

The first query does the job with selecting the correct rank, but I need it to update that rank number into tbl_2.rank or the price into tbl_2.[min-price]

SELECT sku, MIN(Rank) AS Rank
FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.sku = tbl_2.sku AND
tbl_1.price >= tbl_2.price

Either way I can work from there fairly easily.