SQL - select multiple minimum values

Question

I have SQL query:

SELECT min(m.density), m.metal_name
FROM metal m
INNER JOIN storage s ON m.id = s.metal_id
GROUP BY m.metal_name;

As a result I get:

+-----------+--------------+
| m.density | m.metal_name |
+-----------+--------------+
| 1200      | GOLD         |
+-----------+--------------+
| 1200      | TIN          |
+-----------+--------------+
| 1600      | COPPER       |
+-----------+--------------+
| 1800      | PLATINUM     |
+-----------+--------------+

My desired result is something like this (only minimum values of density):

+-----------+--------------+
| m.density | m.metal_name |
+-----------+--------------+
| 1200      | GOLD         |
+-----------+--------------+
| 1200      | TIN          |
+-----------+--------------+

There is a simple way to achieve this result?

Answer 1

You could try something like this:

SELECT DISTINCT
       m.density,
       m.metal_name
FROM   metal   m
JOIN   storage s
  ON   m.id = s.metal_id
WHERE  m.density = (SELECT MIN(a.density)
                    FROM   metal   a
                    JOIN   storage b
                      ON   a.id = b.metal_id);

Select the metals and their densities as far as the density equals the minimum density available in storage.

Answer 2

You can use dense rank function here:

select density, metal_name
from (
  select * , Dense_Rank() over(order by density) rnk
  from metal m
  join storage s on m.id = s.metal_id
)t
where rnk = 1;