Timothy, On Fri, Feb 13, 2009 at 4:45 PM, Little, Timothy <tlit...@thomaspublishing.com> wrote: > Ok, I have a select statement which must return the distinct names, > sorted by ranking (lowest to highest). > > Seems absurdly simple, right, and I'm sure it would be... look at this > example > > CREATE TABLE IF NOT EXISTS HowToExample > ( Name VARCHAR( 32 ), > Ranking INTEGER ) > ENGINE=MyISAM; > > INSERT INTO HowToExample > ( Name, Ranking ) > VALUES > ( 'First', 1 ), > ( 'Second', 2 ), > ( 'Last', 3 ), > ( 'First', 4 ); > > In this case it works correctly, and I get First, Second, Last! YAY > > SELECT Name, > Ranking > FROM HowToExample > GROUP BY Name > ORDER BY Ranking; > > If we CHANGE the contents as follows, however, we get : > > TRUNCATE TABLE HowToExample; > > INSERT INTO HowToExample > ( Name, Ranking ) > VALUES > ( 'First', 4 ), > ( 'Second', 2 ), > ( 'Last', 3 ), > ( 'First', 1 ); > > Then the same SELECT query fails, and gives me Second, Last, First! > > I can't determine an appropriate HAVING clause nor any method of getting > this in a single query...
I'm not 100% sure I understand you, but I notice that you're selecting non-grouped data in a grouped query: http://www.xaprb.com/blog/2006/03/11/many-to-one-problems-in-sql/ Try this: select name, max(ranking) as ranking_max .... order by ranking_max; -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
