Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select by increasing order SQL

Tags:

sql

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.

like image 271
user3291818 Avatar asked Nov 03 '14 22:11

user3291818


People also ask

How do I SELECT in ascending order in SQL?

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.

Can you ORDER BY a SELECT statement?

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.

Can I use ORDER BY and WHERE in SQL?

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.

Can you group by and ORDER BY in SQL?

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.


1 Answers

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;
like image 102
Gordon Linoff Avatar answered Nov 15 '22 11:11

Gordon Linoff