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<m...@good-stuff.co.uk>
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=arch...@jab.org
