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