Select by increasing order SQL

Question

Table:

  id | year |     score 
-----+------+-----------
  12 | 2011 |     0.929
  12 | 2014 |     0.933
  12 | 2010 |     0.937
  12 | 2013 |     0.938
  12 | 2009 |      0.97
  13 | 2010 |     0.851
  13 | 2014 |     0.881
  13 | 2011 |     0.885
  13 | 2013 |     0.895
  13 | 2009 |     0.955
  16 | 2009 |     0.867
  16 | 2011 |     0.881
  16 | 2012 |     0.886
  16 | 2013 |     0.897
  16 | 2014 |     0.953

Desired Output:

  id | year |     score 
-----+------+-----------
  16 | 2009 |     0.867
  16 | 2011 |     0.881
  16 | 2012 |     0.886
  16 | 2013 |     0.897
  16 | 2014 |     0.953

I'm having difficulties in trying to output scores that are increasing in respect to the year. Any help would be greatly appreciated.

Answer 1

So you want to select id = 16 because it is the only one that has steadily increasing values.

Many versions of SQL support lag(), which can help solve this problem. You can determine, for a given id, if all the values are increasing or decreasing by doing:

select id,
       (case when min(score - prev_score) < 0 then 'nonincreasing' else 'increasoing' end) as grp
from (select t.*, lag(score) over (partition by id order by year) as prev_score
      from table t
     ) t
group by id;

You can then select all "increasing" ids using a join:

select t.*
from table t join
     (select id
      from (select t.*, lag(score) over (partition by id order by year) as prev_score
            from table t
           ) t
      group by id
      having min(score - prev_score) > 0
     ) inc
     on t.id = inc.id;