Stefan Schwarzer wrote:
>> SELECT year, value FROM ...
>
> I feel ashamed.... such a simple solution... gush.... Thanks for that!
>
> 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...
>
> How would I do that? I really have no clue...
>
> (my table looks something like this:
>
> id_variable | year | value | id_country
> ---------------------------------------
> 1 | 2001 | 123 | 1
> 1 | 2002 | 125 | 1
> ....
>
> 2 | 1980 | 83 | 1
> 2 | 1981 | 89 | 1
> ....
>
> )
>
> Thanks for any hints,
As others have noted, the query *can* be written. But it appears to me
that you are struggling against your table layout. Before struggling
with ever more complicated queries, I'd consider restructuring your
table(s). There are many possibilities depending on the current nature
of your data, how you expect it to change and the queries you expect to
run against it. For example:
country_id
data_year
gdp
fish_catch
Then your query may be as simple as, say:
select min(year) from your_table
where country_id = xxx
and gdp is not null
and fish_catch is not null;
or
select year, gdp, fish_catch from your_table
where country_id = xxx
and gdp is not null
and fish_catch is not null
order by year desc, gdp desc, fish_catch desc
limit 1;
Alternately, you could have a gdp table and a fish_catch table which
would be easily joined to give the same result.
Cheers,
Steve
