List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:December 3 2010 5:35pm
Subject:Re: Lowest non-zero number
View as plain text  
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<mark@stripped>
>> 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
Thread
Lowest non-zero numberMark Goodge3 Dec
  • Re: Lowest non-zero numberPaul Halliday3 Dec
    • Re: Lowest non-zero numberMark Goodge3 Dec
      • Re: Lowest non-zero numberMySQL)3 Dec
  • Re: Lowest non-zero numberMySQL)3 Dec