Re: Find min year and min value - Mailing list pgsql-general

From Stefan Schwarzer
Subject Re: Find min year and min value
Date
Msg-id 6B863DEC-202B-4064-AAEF-ACB2ED369B02@grid.unep.ch
Whole thread Raw
In response to Re: Find min year and min value  (Michael Glaesemann <grzm@seespotcode.net>)
Responses Re: Find min year and min value
Re: Find min year and min value
List pgsql-general
>> 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
4                           1980               6.6
4                           1981               7.0
...
6                           1980               5.1


Thanks for any advice!

Stef


pgsql-general by date:

Previous
From: Ow Mun Heng
Date:
Subject: Q: migrating from serverA(gentoo) to serverB(centos) 8.2.4
Next
From: Miroslav Šulc
Date:
Subject: Re: ERROR: variable not found in subplan target lists