CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
Mumfordwiz
Mumfordwiz

Reputation: 1545

sql - using MIN function

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.

Upvotes: 0

Views: 123

Answers (3)

xQbert
xQbert

Reputation: 35323

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

Upvotes: 2

DVT
DVT

Reputation: 3127

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

Upvotes: 0

CDspace
CDspace

Reputation: 2689

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.

Upvotes: 2

Related Questions