Get cheapset prices in price table

I have a price table. I want to list the cheapest price of the products with the same IDs in my table. How can I do it?

Table Name : prices

userID| productsID  | price  | stock | maks
-------------------------------------------
 1    | C120221     |  100  | 3      | 1
 2    | C120221     |  200  | 5      | 5
 3    | BR120221    |  500  | 7      | 3
 4    | BR120221    |  600  | 9      | 0
 5    | BR120221    |  700  | 11     | 2

SQL

    SELECT
        MIN(price ) AS price ,
        GROUP_CONCAT(userID) AS userID
        FROM prices
        WHERE price > 0 
        GROUP BY productsID  
        ORDER BY price ASC

In this case I want to list all the info of the user with the cheapest price. In the current query, I can get the data in the productsID and price fields, but I cannot get the data in the userID, stock and max fields of the user with these data. Where is the problem?

Solution 1:

You are probably trying to find the line or lines that contain the lowest price.

SELECT
  prices.*
FROM
  (
    SELECT
      MIN(price) AS price,
      productsID
    FROM
      prices
    WHERE
      price > 0
    GROUP BY
      productsID
  ) AS t1
  JOIN prices ON prices.price = t1.price
  AND prices.productsID = t1.productsID