Yes year is a function - you can escape them using [value] and [year]
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 17 June 2003 7:04 PM
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
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
