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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
