-
SQL query
I need help in producing a sql query for the following:
i got a table [QUOTE] with columns
QuotationID | ItemCode | Price | SupplierID
QuotationID is a Key.
Many suppliers can supply multiple items at different price.
Qn: I want to get the QuotationID for each ItemCode with the lowest price.
I understand that
Select min(price), ItemCode
From Quote
Group by ItemCode
will generate me the lowest price for each ItemCode, but how can I modify this query such that I will know which QuotationID supplies me with this lowest price.
-
In T-SQL
SELECT q.QuotationID
from quote q
join (Select min(price) price, ItemCode
From Quote
Group by ItemCode
) minprice on
q.ItemCode = minprice.ItemCode and
q.price = minprice.price
should do what you need.
Be careful you will get multiple records back if there is more than one supplier with the same price per item.
-
Thanks !!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|