Re: Find min year and min value

On Oct 5, 2007, at 4:11 , Stefan Schwarzer wrote:

>>> Alternately, you could have a gdp table and a fish_catch table which
>>> would be easily joined to give the same result.
>>
>> Expanding on this:
>>
>> create table fish_catches (country text not null,
>>                            data_year date not null,
>>                            primary key (country, data_year),
>>                            fish_catch numeric not null);
>>
>> create table gdp (country text not null reference countries
>>                   data_year date not null,
>>                   primary key (country, data_year),
>>                   gdp numeric not null);
>>
>> This makes your queries quite simple:
>>
>> select country, data_year, fish_catch, gdp
>> from fish_catches
>> natural join gdp
>> where country = :country
>> order by data_year
>> limit 1;
>
> Hmmm..... Don't really get that query working. My SQL looks like
> this now:
>
> SELECT
>    id_country,
>    year,
>    value
> FROM
>   internet_users
> NATURAL JOIN
>   gdp
> WHERE
>    id_country = 8
> ORDER BY
>    year
> LIMIT
>    1
>
> But there is no result.
>
> My table looks like this (for each variable one table):
>
> id_country            year                value

The natural join operator joins on common columns: if columns are
named "value" in both tables, the join condition is (id_country,
year, value) = (id_country, year, value). In the example I provided
above, the tables were fish_catches {country, year, fish_catch} and
gdp {country, year, gdp}: the join condition is (country, year) =
(country, year).

Also, note that there are *four* output columns in the query I used:
{country, data_year, fish_catch, gdp}. You've only got three, which
is bound to be confusing.

In your case you can use subqueries to rename the columns or an
explicit join:

-- using subqueries
select id_country, year, internet_users, gdp
   from (select id_country, year, value as internet users
           from internet_users) i
   natural join (select id_country, year, value as gdp
                   from gdp) g
   where id_country = 8
   order by year
   limit 1

-- using an explicit join

select id_country, year, internet_users.value as internet_users,
gdp.value as gdp
from internet_users
join gdp using (id_country, year)
where id_country = 8
order by year
limit 1


Michael Glaesemann
grzm seespotcode net