Select ascending order of 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 

Output Required:

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

I am having difficulty trying to derive estimates that are increasing with respect to the year. Any help would be greatly appreciated.

Answer 1

So you want to choose id = 16, because this is the only one that constantly increments values.

Many versions of SQL lag() support can help solve this problem. You can determine for a given identifier if all values ​​increase or decrease 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; 

Then you can select all the "increasing" identifiers using the connection:

 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;