Re: Find min year and min value

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

Hmmm.... I am somewhat surprised to here so. After being told in this
forum how "bad" my old table design was, I changed it to the current
(which is less than alpha). Perhaps to summarize: Having 500
statistical global national variables for about 240 countries/
territories. Need to do regional aggregations, per Capita
calculations and some completeness computations on-the-fly.

The design was a table like this for each variable:

id_country | 1970 | 1971 | ... | 2004 |
2005
-------------------------------------------------------------------
1 | NULL | 36 | ... |
42 | 45
2 ......

The new like this:

id_variable | year | value | id_country
---------------------------------------
1 | 2001 | 123 | 1
1 | 2002 | 125 | 1
1 | 2003 | 128 | 1
1 | 2004 | 132 | 1
1 | 2005 | 135 | 1

1 | 2001 | 412 | 2
1 | 2002 | 429 | 2
1 | 2003 | 456 | 2
1 | 2004 | 465 | 2
1 | 2005 | 477 | 2

....

2 | 1980 | 83 | 1
2 | 1981 | 89 | 1
....

I thought (and did ask) about the possibility to put nevertheless -
with the new table design - the variables into different tables, but
nobody really got my on a track for that. So I thought the most
"common" way would be to have this central table.

But I am at a stage where I still can change - and would very much
like to get your advice.

Thanks a lot!

Stef

____________________________________________________________________

Stefan Schwarzer

Lean Back and Relax - Enjoy some Nature Photography:
http://photoblog.la-famille-schwarzer.de

Appetite for Global Data? UNEP GEO Data Portal:
http://geodata.grid.unep.ch
____________________________________________________________________