CODEWITHSUNDEEP
sqlpostgresql
Rakshit Rao
Rakshit Rao

Reputation: 53

How to check if entries in table in postgres are increasing

I've a table temp(a int). It has values some values stored in it. How can I know if all those values are strictly increasing or not?

For example, temp may contain 1,2,3- values in strictly increasing sequence or 1,1,2- not strictly increasing. Thanks!

Upvotes: 1

Views: 760

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

SQL tables represent unordered sets. So, if your table has only one column, there is no answer -- the question has no meaning because there is no ordering.

If I assuming you have an ordering column, you can simply use lag(). For a flag on the table, here is logic:

select (count(*) > 0) as not_all_increasing
from (select t.*, lag(val) over (order by <orderingcol>) as prev_val
      from t
     ) t
where prev_val > val;

Just use select t.* if you want to see the values that are non-increasing.

Upvotes: 5

Related Questions