r-276086 (3/14/2013)
Hi I have the following table (created from a view):BaseSupplierID__SupplierID___ProductID__Price
3_______________3___________1________11
3_______________4___________1________11
3_______________5___________1________15
3_______________6___________1________10
4_______________3___________2________16
4_______________4___________2________10
5_______________5___________3________16
5_______________8___________3________14
5_______________9___________3________10
Basically each product can be supplied from multiple supplies yet there is a base supplier (which in theory should be the cheapest). I need to create a query that would show me the products where the base supplier was not the cheapest for their products? Any pointers?? Thanks
seems like a homework question, so I'll try and help you learn, instead of spoon feeding an answer;
the idea here is to use the MIN() and MAX() functions and a GROUP BY clause.
in this case, since you are looking for the cheapest supplier for a specific product;
an alternative is to use the ROW_NUMBER() OVER(PARTITION BY .. ORDER BY..) to order the data, and look at the min or max values that way as well.
let us see what you've tried so far so we can help you understand the concepts.
If you can supply the CREATE TABLE... and INSERT INTO commands, we can give you working examples or edits of your code to help you better.
something like this is what i mean:
With MySampleData (BaseSupplierID,SupplierID,ProductID,Price)
AS
(
SELECT 3,3,1,11 UNION ALL
SELECT 3,4,1,11 UNION ALL
SELECT 3,5,1,15 UNION ALL
SELECT 3,6,1,10 UNION ALL
SELECT 4,3,2,16 UNION ALL
SELECT 4,4,2,10 UNION ALL
SELECT 5,5,3,16 UNION ALL
SELECT 5,8,3,14 UNION ALL
SELECT 5,9,3,10
)
select * from MySampleData
Lowell