Re: Find min year and min value

On Tue, Oct 02, 2007 at 04:29:02PM +0200, Stefan Schwarzer wrote:
> If I want to find the "common smallest year" for two given variables
> (say, I have years 1970, 1971,.... 2005 for variable 1 (GDP) and
> 1980, 1981,... 2003) for variable 2 (Fish Catch) ). It should come up
> with 1980 for a given country, if there is a value for that year in
> both variables. Otherwise 1981, etc...
> How would I do that? I really have no clue...
> (my table looks something like this:
> id_variable   |    year    |    value     |    id_country
> ---------------------------------------
>         1             |   2001    |     123       |   1

select min(year) from (select year from table where id_variable in (1,2)
group by year having count(distinct id_variable) = 2) x;

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)