List:General Discussion« Previous MessageNext Message »
From:Mark Goodge Date:December 3 2010 5:16pm
Subject:Re: Lowest non-zero number
View as plain text  
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
-- 
http://mark.goodge.co.uk
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