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=arch...@jab.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=arch...@jab.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=arch...@jab.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=arch...@jab.org
