Results 1 to 3 of 3

Thread: SQL query

  1. #1
    Join Date
    Jan 2003
    Location
    Singapore
    Posts
    2

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    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.

  3. #3
    Join Date
    Jan 2003
    Location
    Singapore
    Posts
    2
    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
  •