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.
The ORDER BY command is used to sort the result set in ascending or descending order. The ORDER BY command sorts the result set in ascending order by default. To sort the records in descending order, use the DESC keyword.
SQL queries initiated by using a SELECT statement support the ORDER BY clause. The result of the SELECT statement is sorted in an ascending or descending order.
The ORDER BY clause must come after the WHERE, GROUP BY, and HAVING clause if present in the query. Use ASC or DESC to specify the sorting order after the column name. Use ASC to sort the records in ascending order or use DESC for descending order.
When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With