Re: Find "smallest common year"

I really tried it out. I changed my whole database to the "id-country
| year | value" - format. And then tried to build my SQL queries. But
it was really, really difficult, and sometimes impossible (for me) to
get what I wanted.

Actually, it remains quite difficult for me to remember the actual
problems I had with it.

But, for example, I need to aggregate the national data on-the-fly to
their regions. I need to calculate per Capita data on-the-fly for
each variable. Although, one would say this should be simple to
accomplish, me and a semi-professional database expert could hardly
solve these things.

In one case we came up with as many sub-selects as years were
available (or selected by the user) (that can be up to 60 years). Is
this "efficient" SQL programming?

What would you recommend for say, 500 global national statistical
variables, 500 regional and 500 subregional and 500 global
aggregations? Years being covered having something between 10 and 60
years for each of these variables. All available for 240 countries/
territories.

Thanks for any recommendations!

Stef

Stefan Schwarzer wrote:
Sorry,

I forgot to mention my table design, which is like this:

name 2001 2002 2003 2004 2005
-----------------------------------------------------------------
Afghanistan ....
Albania ....

(Yep, I know, bad table design.... :-)). I tried to change it to the
more common "id | year | value" format, but there were too many SQL
problems afterwards for the different queries/calculations we need to
have....)

May I suggest that you concentrate on solving *those* problems
instead of
the programmatically trivial computation of lowest common value? Notice
that a *really trivial* programming exercise becomes highly involved in
your case --- if I'm understanding correctly what you have, I assume
you'd
have to check one by one the fields for NULL or non-NULL values ---
that's
intolerably ugly, IMHO, and it is a very simple and direct
consequence of
an as-unsound-as-it-gets db/table design.

____________________________________________________________________

Stefan Schwarzer

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

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