From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Find min year and min value |
Date: | 2007-10-02 14:53:23 |
Message-ID: | 47025B63.5080300@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2007-10-02 14:53:34 | Re: Find min year and min value |
Previous Message | Stefan Schwarzer | 2007-10-02 14:29:02 | Re: Find min year and min value |