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

On Oct 2, 2007, at 11:10 , Steve Crawford wrote:

> As others have noted, the query *can* be written. But it appears to me
> that you are struggling against your table layout.

The current schema he has is commonly called EAV (entity-attribute-
value) and is generally frowned upon. Now, in his particular case it
may be justified if the "value" column values are actually all of the
same type, such as currency amounts for each category. If this is the
case, I suggest renaming the column to be more descriptive of what is
actually stored: likewise the id_variable column.

> 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

This would be one way to do it. However, each time you add a new
category you'd need to add a new column to the table: not very
flexible. You can also have the same functionality by adding a new
table for each category:


> 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;

or

select country, data_year, fish_catch, gdp
from fish_catches
natural join gdp
natural join (select country, min(data_year) as data_year
               from gdp
               natural join fish_catch
               group by country) min_data_year
where country = :country;

Splitting categories into separate tables also eliminates the
necessity of worrying about NULL, which can lead to unexpected
behavior if you aren't careful.

Michael Glaesemann
grzm seespotcode net