In SQL, how do I get all rows where a column's value is the lowest in the table?

Question

I am a newbie to SQL, I am using this query to look for the minimum value in the field weight of my table.

SELECT product_id, 
       MIN(weight) 
  FROM table 
 WHERE 1;

It does show one field with the min value, but only one? But I have many products with the same minimum weight. Is there a way I could specify that I need to show all other products?

Answer 1

select * from table where weight = (select MIN(weight) from table)

Answer 2

This may be what you're asking for:

SELECT product_id FROM table WHERE weight = (SELECT MIN(weight) FROM table);

As you might guess, this will select all prodict_ids where the weight is equal to the minimum weight in the table.

Answer 3

Not sure which one exactly you want, but either of these should do the trick:

SELECT product_id, MIN(weight) FROM table WHERE 1 GROUP BY product_id

(List all product IDs and the minimum weight per product ID)

SELECT product_id, weight FROM table WHERE weight = (SELECT min(weight) FROM table)

(Find all product IDs where the weight equals the minimum weight)

SELECT min(weight) FROM table;

(Find the absolute minimum weight, and that's that)