Ahh... you didn't say you only wanted the latest year in your first message, so that's what prompted the answer.
> Thanks, > But it was actually the latest values I was interested in. > I solved it on > my own, with doing a subquery locating the latest years > for each > countries, and then joining that to the main table. > Something like: > SELECT > * > FROM > myTable INNER JOIN ( > SELECT countryName, Max(Year) as MaxYear FROM mytable) > joinTable > ON myTable.countryName=joinTable.countryName > AND myTable.Year=joinTable.MaxYear > From that query, I would only get the latest available > values for each > country. > ---------------------------------------------------------- > --- > Hugo Ahlenius E-Mail: > [EMAIL PROTECTED] > Project Officer Phone: +46 8 > 230460 > UNEP GRID-Arendal Fax: +46 8 > 230441 > Stockholm Office Mobile: +46 733 > 467111 > WWW: > http://www.grida.no > ---------------------------------------------------------- > --- > | -----Original Message----- > | From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] > | Sent: Tuesday, June 17, 2003 10:54 > | To: CF-Talk > | Subject: Re: OT: SQL problem > | > | > | Typically it would be something like this: > | > | SELECT countryname, max(value), year > | FROM mytable > | GROUP BY countryname, year > | ORDER BY countryname, year > | > | hth > | > | > Hi, > | > | > Anyone with some time for a little SQL question? > | > | > I want to have a SELECT that gives me the highest > values > | > in a table > | > | > The table looks like this: > | > CountryName, Value, Year > | > | > And the data something like: > | > Sweden, 80, 1970 > | > Sweden, 20, 1999 > | > Denmark, 10, 1962 > | > Denmark, 15, 1992 > | > | > (etc) > | > | > What I want to get from the query is the rows: > | > Sweden, 20, 1999 > | > Denmark, 15, 1992 > | > | > Anyone? > | > | > > ---------------------------------------------------------- > | > --- > | > Hugo Ahlenius E-Mail: > | > [EMAIL PROTECTED] > | > Project Officer Phone: +46 8 > | > 230460 > | > UNEP GRID-Arendal Fax: +46 8 > | > 230441 > | > Stockholm Office Mobile: +46 733 > | > 467111 > | > WWW: > | > http://www.grida.no > | > > ---------------------------------------------------------- > | > --- > | > | > | > | > | > ########################################### > | > | > This message has been scanned by F-Secure Anti-Virus > for > | > Microsoft > | > Exchange. > | > For more information, connect to > http://www.F-Secure.com/ > | > | > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > | > ~~~~~~~~~~~| > | > Archives: > | > > http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 > | > Subscription: > http://www.houseoffusion.com/cf_lists/index. > | > cfm?method=subscribe&forumid=4 > | > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > | > | > Get the mailserver that powers this list at > | > http://www.coolfusion.com > | > | > Unsubscribe: > http://www.houseoffusion.com/cf_lists/uns >> ubscribe.cfm?user=633.558.4 > s. isaac dealey 972-490-6624 > new epoch http://www.turnkey.to > lead architect, tapestry cms http://products.turnkey.to > tapestry api is opensource http://www.turnkey.to/tapi > certified advanced coldfusion 5 developer > http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~~| > Archives: > http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 > Subscription: http://www.houseoffusion.com/cf_lists/index. > cfm?method=subscribe&forumid=4 > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > Get the mailserver that powers this list at > http://www.coolfusion.com > Unsubscribe: http://www.houseoffusion.com/cf_lists/uns > ubscribe.cfm?user=633.558.4 s. isaac dealey 972-490-6624 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
