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/unsubscribe.cfm?user=89.70.4
