Select min returns empty row

Question

Query

select min Price, min year from tblProduct

returns empty ("") results. I only want results in numbers. How do I do that?

Example:

tblProduct
Price   Year

1000    2008
2000    2009
500  2001
        2005
200  2000

The result is Price "", year 2000. I want the result to be Price 200, year 2000

Answer 1

First of all, what is your RDBMS? For the ones I tried (MySQL and PostgreSQL), MIN ignores NULL.

Therefore, I assume your column is of type varchar or text. Which doesn't make sense for a price, so I suggest that you change it to a numeric type.

If you don't want to, then I'd use something like this :

SELECT MIN(price), MIN(year)
FROM tblProduct
WHERE price <> ''

Which would return (200, 2000). But really, it doesn't make sense. You want to use a numeric type and you want to use NULL instead of using an empty string.

Answer 2

Try this one

SELECT     MIN(Price) AS Price, MIN(Year) AS Year
FROM         tblProduct
HAVING      (MIN(Price) IS NOT NULL) OR
                      (MIN(Price) <> '')