none
Problem with query RRS feed

  • Question

  • Hello. I'm just starting with MySQL. I think I know quite a lot comparing to my practice. However I have a trouble with one query. 

    I have two tables in database. Let's say that one of those is table of products.

    |ID|Product |
    |1 |Product1|
    |2 |Product2|
    |3 |Product3|

    The second one is table with different prices of this products.
    |ID|ProductID|Price|
    |1 |1        |20   |
    |2 |1        |30   |
    |3 |1        |35   |
    |4 |1        |50   |
    |5 |2        |10   |
    |6 |2        |60   |
    |7 |2        |5    |
    |8 |3        |2    |
    |..|..       |..   |

    The problem is I don't know how to match each product to its lowest price and the second lowest price.
    For example, the result of query should look like this:

    |Product |Lowest price|Second lowest price|
    |Product1|20          |30                 |
    |Product2|5           |10                 |
    |...     |...         |...                |

    I will appreciate any help from you. 
    • Moved by Olaf HelperMVP Saturday, December 29, 2018 7:14 AM Not MS related
    Wednesday, October 3, 2018 6:36 PM

All replies

  • First of all this is not MySQl forum

    This forum deals with MS SQL Server product.S o solutions given here are T-SQL centric which is MS SQL Server programming dialect. So your will always get best resulst by posting in mysql forums

    That being said ANSI based query syntax should work fine in most systems

    As per that I think you can try like below and see if it works

    SELECT p.Product,MIN(pp.Price) AS LowestPrice,MAX(Price) AS SecondLowestPrice
    FROM Product p
    INNER JOIN ProductPrices pp
    ON pp.ProductID = p.ID
    WHERE 1 >= (
    SELECT COUNT(*)
    FROM ProductPrices
    WHERE ProductID = pp.ProductID
    AND Price < pp.Price
    )GROUP BY p.Product
    if not please try your luck in some MySQL forums


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page



    Wednesday, October 3, 2018 7:24 PM