Make MIN() return multiple rows?

Is there a way to return more than one row based on the idea of this query:

SE开发者_如何学GoLECT MIN(colname) AS value FROM table_name

Thanks Dave

If you're trying to select the rows with the N (say, 10) smallest values in some column, you could do something like:

SELECT * FROM table_name ORDER BY colname ASC LIMIT 10;

Apologies if I've misunderstood the question.

No, there is no way to return more than 1 rows for this very query (the one you included in the example)

If you want to be doubly sure, add a "LIMIT 1" at the end. (that would limit the result set to 1 row, but not required here)

EDIT:

To answer your question "Is there any other query that can return say 5 rows based on each row having MIN values in one column", yes there is. You need to use a 'group by' syntax, for example:

SELECT category, MIN(price) AS value FROM table_name group by category

To select multiple rows one solution is to find all rows with values that match the previously determined minimum value:

SELECT *
FROM tableA
JOIN
   (SELECT MIN(colname) AS minvalue FROM tableA) B
ON tableA.colname = B.minvalue

This solution differs from GROUP BY in that it will not otherwise aggregate (or limit) the data in the resultset. In addition, this particular solution does not consider the case of NULL being the "minimum value".

Happy coding.