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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
