Re: Find min year and min value - Mailing list pgsql-general

From Stefan Schwarzer
Subject Re: Find min year and min value
Date
Msg-id BA560C64-608A-44B7-BD8D-0581E825F00D@grid.unep.ch
Whole thread Raw
In response to Re: Find min year and min value  (Richard Huxton <dev@archonet.com>)
Responses Re: Find min year and min value  (Richard Huxton <dev@archonet.com>)
Re: Find min year and min value  (Michael Glaesemann <grzm@seespotcode.net>)
Re: Find min year and min value  (Steve Crawford <scrawford@pinpointresearch.com>)
Re: Find min year and min value  (hubert depesz lubaczewski <depesz@depesz.com>)
List pgsql-general
> SELECT year, value FROM ...

I feel ashamed.... such a simple solution... gush.... Thanks for that!

Unfortunately it doesn't stop there...

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

)

Thanks for any hints,

Stef

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Strange discrepancy in query performance...
Next
From: Richard Huxton
Date:
Subject: Re: Find min year and min value