I want to get the first entry (datum, crop_type) where a specific crop is cultivated. the list I want to extract that information from is already ordered by year and month, however I have no idea, how to set up the window function properly. I think the easiest way would be to go back from each respective harvest (where yield_fm is != 0) and crop type is != 999999, but as I said, no idea how to do this.
snippet from my table:
"datum","crop_type","yield_fm"
1996-01-01,3,0
1996-02-01,3,0
1996-03-01,3,0
1996-04-01,3,0
1996-05-01,3,0
1996-06-01,3,0
1996-07-01,3,0
1996-08-01,3,9.5
1996-09-01,999999,0
1996-10-01,999999,0
1996-11-01,999999,0
1996-12-01,999999,0
1997-01-01,999999,0
1997-02-01,999999,0
1997-03-01,999999,0
1997-04-01,16,0
1997-05-01,16,0
1997-06-01,16,0
1997-07-01,16,0
1997-08-01,16,0
1997-09-01,16,53.7
1997-10-01,999999,0
1997-11-01,3,0
1997-12-01,3,0
1998-01-01,3,0
1998-02-01,3,0
1998-03-01,3,0
1998-04-01,3,0
1998-05-01,3,0
1998-06-01,3,0
1998-07-01,3,0
1998-08-01,3,8
1998-09-01,999999,0
1998-10-01,3,0
1998-11-01,3,0
1998-12-01,3,0
1999-01-01,3,0
1999-02-01,3,0
1999-03-01,3,0
1999-04-01,3,0
1999-05-01,3,0
1999-06-01,3,0
1999-07-01,3,0
1999-08-01,3,8
SQL tables represent unordered sets. There is no ordering, unless columns specify the ordering.
If you want the first row per crop type, then in Postgres, I would recommend distinct on:
select distinct on (crop_type) t.*
from t
order by crop_type, datum;
distinct on is Postgres extension. It returns one row for each combination in the distinct on clause -- in this case, one per crop_type.
The row returned is the first one encountered, base don the order by clause. The first columns in the order by should match the columns in the distinct on. The remaining columns then define "first".