gotta use the As clause | SELECT CountryName, Value, Year | FROM table | WHERE | (CountryName, Year) IN ( | SELECT CountryName, MAX(Year) As Year | FROM table | GROUP BY CountryName | )
-----Original Message----- From: Hugo Ahlenius [mailto:[EMAIL PROTECTED] Sent: Tuesday, 17 June 2003 7:11 PM To: CF-Talk Subject: RE: OT: SQL problem The columns are (of course) named something else. Perhaps I should have used something else? Jochem, your suggestion looks cleaner than mine, but SQL Server 2k chokes on your (CountryName, Year) in the WHERE clause. I am actually not sure what that does... ? ------------------------------------------------------------- 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: Jochem van Dieten [mailto:[EMAIL PROTECTED] | Sent: Tuesday, June 17, 2003 11:04 | To: CF-Talk | Subject: Re: OT: SQL problem | | | Hugo Ahlenius wrote: | > | > I want to have a SELECT that gives me the highest values in a table | > | > The table looks like this: | > CountryName, Value, Year | | Aren't value and year reserved words in SQL? | | | > 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 | | SELECT CountryName, Value, Year | FROM table | WHERE | (CountryName, Year) IN ( | SELECT CountryName, MAX(Year) | FROM table | GROUP BY CountryName | ) | | Jochem | | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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
