I'm trying to use MIN function in SQL, but I keep getting a lot of rows, and not just the MIN row.
I'm using this tutorial http://www.w3schools.com/SQl/trysql.asp?filename=trysql_func_min
running this query
SELECT
P.ProductID,
MIN(Price) AS SmallestOrderPrice
FROM Products P
GROUP BY ProductID;
I want to get just the row that has the min price from all prices, and it's corresponding product. If more than one product has the same price, I would like it to return both products.
Perhaps you want the productid and the min price across all products.
The window function way.
SELECT *
FROM (SELECT P.ProductID, Price AS SmallestOrderPrice, row_number() over (order by price asc ) RN
FROM Products P)
WHERE RN = 1
Or my old standard... (Generally works across all platforms and useful if you need data from the 2nd table, you don't appear to)
SELECT *
FROM products P
INNER JOIN (Select min(price) mp from products) P2
on P.Price = P2.MP
or using the exists method... (Generally Compliant across all DBMS and likely fastest)
SELECT P.*
FROM products P
WHERE EXISTS (SELECT min(price) mp
FROM products P2
WHERE P2.Price = P.Price)
I'm not sure if the having would work but it seems like it should
SELECT
P.ProductID,
MIN(Price) AS SmallestOrderPrice
FROM Products P
GROUP BY ProductID
HAVING min(Price) = price
Below is your query, with TOP 1 and ORDER BY Price ASC added.
The ORDER BY will cause your current returns to be ordered in ASCending order, putting the smallest price at the top. Then the TOP 1 will limit it to just that smallest price.
SELECT TOP 1
P.ProductID,
MIN(Price) AS SmallestOrderPrice
FROM Products P
GROUP BY ProductID
ORDER BY Price ASC;
Note, if there are multiple products with the same minimum price, this won't return all of them. In that case, xQbert's queries are probably best.
Min Price for all the rows.
SELECT MIN(Price)
FROM Products
And if you want ALL the ID also
SELECT
p.ProductID
, mp.MinPrice AS SmallestOrderPrice
FROM
Products P
CROSS APPLY (SELECT MIN(Price) MinPrice FROM Products ) AS mp
WHERE
p.Price = mp.MinPrice
