Stefan Schwarzer wrote:
>> SELECT year, value FROM ...
>
> I feel ashamed.... such a simple solution... gush.... Thanks for that!
Can be easy to over-complicate things when you've been thinking about
them too long.
> Unfortunately it doesn't stop there...
>
> If I want to find the "common smallest year" for two given variables
> (say, I have years 1970, 1971,.... 2005 for variable 1 (GDP) and 1980,
> 1981,... 2003) for variable 2 (Fish Catch) ). It should come up with
> 1980 for a given country, if there is a value for that year in both
> variables. Otherwise 1981, etc...
In that case you will need two subqueries, but it's just a matter of
converting your description to SQL.
SELECT
yr1,
gdp.val1 AS gdp_val,
fish_catch.val2 AS fish_catch_val
FROM
(SELECT year AS yr1, value AS val1 FROM data WHERE id_variable = 1
) AS gdp,
(SELECT year AS yr2, value AS val2 FROM data WHERE id_variable = 2
) AS fish_catch
WHERE
gdp.yr1 = fish_catch.yr2
ORDER BY
gdp.yr1
LIMIT 1;
Here I've aliases (renamed) the columns and the sub-queries, but I'd
probably just alias the sub-queries in real-life.
You could write it as a JOIN if you prefer that style, or use the MIN()
aggregate (although I'd guess that the ORDER BY/LIMIT might prove faster).
So, I'd perhaps use:
SELECT gdp.year, gdp.val AS gdp_val, fish_catch.val AS fish_catch_val
FROM
(SELECT year,value FROM data WHERE id_variable=1) AS gdp
JOIN
(SELECT year, value FROM data WHERE id_variable=2) AS fish_catch
USING (year)
ORDER BY gdp.year
LIMIT 1;
--
Richard Huxton
Archonet Ltd
