Lowest non-zero number

Given a table containing a range of INT values, is there any easy way to
select from it the lowest non-zero number?

Obviously, MAX(column) will return the highest, but MIN(column) will
return 0 if any row contains a 0, which isn't what I want.

Any clues?

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Lowest non-zero number

On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodge wrote:
> Given a table containing a range of INT values, is there any easy way to
> select from it the lowest non-zero number?
>

SELECT number FROM table WHERE number > 0 ORDER BY number ASC LIMIT 1;

?
--
Paul Halliday
Ideation | Individualization | Learner | Achiever | Analytical
http://www.pintumbler.org

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Lowest non-zero number

On 12/3/2010 11:50, Mark Goodge wrote:
> Given a table containing a range of INT values, is there any easy way to
> select from it the lowest non-zero number?
>
> Obviously, MAX(column) will return the highest, but MIN(column) will
> return 0 if any row contains a 0, which isn't what I want.
>
> Any clues?
>
> Mark

SELECT MIN(column) FROM table WHERE column>0 ?

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Lowest non-zero number

On 03/12/2010 16:56, Paul Halliday wrote:
> On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodge wrote:
>> Given a table containing a range of INT values, is there any easy way to
>> select from it the lowest non-zero number?
>>
>
> SELECT number FROM table WHERE number> 0 ORDER BY number ASC LIMIT 1;

Sorry, I should have said that I need to do this as part of a query
which returns other data as well, including data from the rows which
have a 0 in this column. So I can't exclude them with the WHERE clause.

What I'm actually doing is something like this:

SELECT
name,
AVG(score) as average,
count(score) as taken
FROM tests GROUP BY name

and I want to extend it to something like this:

SELECT
name,
AVG(score) as average,
COUNT(score) as attempts,
SUM(score = 0) as failed,
SUM(score > 0) as passed,
MAX(score) as best_pass,
...... as lowest_pass
FROM tests GROUP BY name

and I need an expression to use in there to get the lowest non-zero
value as lowest_pass.

Does that make sense? And, if so, is there any easy way to do it?

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Lowest non-zero number

On 12/3/2010 12:16, Mark Goodge wrote:
> On 03/12/2010 16:56, Paul Halliday wrote:
>> On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodge
>> wrote:
>>> Given a table containing a range of INT values, is there any easy way to
>>> select from it the lowest non-zero number?
>>>
>>
>> SELECT number FROM table WHERE number> 0 ORDER BY number ASC LIMIT 1;
>
> Sorry, I should have said that I need to do this as part of a query
> which returns other data as well, including data from the rows which
> have a 0 in this column. So I can't exclude them with the WHERE clause.
>
> What I'm actually doing is something like this:
>
> SELECT
> name,
> AVG(score) as average,
> count(score) as taken
> FROM tests GROUP BY name
>
> and I want to extend it to something like this:
>
> SELECT
> name,
> AVG(score) as average,
> COUNT(score) as attempts,
> SUM(score = 0) as failed,
> SUM(score > 0) as passed,
> MAX(score) as best_pass,
> ..... as lowest_pass
> FROM tests GROUP BY name
>
> and I need an expression to use in there to get the lowest non-zero
> value as lowest_pass.
>
> Does that make sense? And, if so, is there any easy way to do it?
>
> Mark
Try this:

MIN(if(score=0,NULL,score)) as lowest_pass

That should either give you a null or a score. There is always the
possibility that someone never had a score above zero. This should
handle it.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org