2

Say you have a table in PostgreSQL 11 with sets of values something like this below.

12 18
11 12
5  12
9  18
7  19
14 18
11 16
6  12
4  11
5  9

Is there a way to do a select without using sub-selects or select by using functions or trigger/functions to find where each value is lower than previous or could be higher than previous but this example is lower than previous (could be 2 row or 15+ rows) in both column and first column went higher than last row or both first and second column to terminate the select making the condition positive?

I do not know of a way to have present variables inside of a select. If I could keep a look back variable then I should be able to do this. I am wondering if anyone out there has ever come across this and made it work inside a select or they went outside of the database into GoLang / Python / C++ / Perl / PHP / etc. Very simple to do once I leave the database but I would really like to keep this inside PostgreSQL 11.

  • 1
    where each value is lower than previous or could be higher than previous To make a sense to "previous" term you MUST specify the expression for records sorting (the physical order doesn't fit !!!). – Akina Sep 16 at 4:31
  • The example I posted is only for lower. – user10078199 Sep 16 at 4:47
  • I did play with LAG but I was not able to make it work. Would not return correct values for look back reference to current row value. Current value could be evaluated to older value. – user10078199 Sep 16 at 5:16
  • 1
    Show full table's DDL and full sample data (not fields in interest only). Would not return correct values for look back reference to current row value. What ORDER BY clause have you used in LAG() frame specification? – Akina Sep 16 at 6:27
2

As Akina has pointed out in comments LAG() function requires an ORDER BY clause to show the properly values.

Given the next example:

CREATE TABLE t (id int, bar int);

INSERT INTO t VALUES
(1, 7),
(2, 8),
(3, 30),
(4, 25),
(5, 24),
(6, 24),
(7, 35),
(8, 40);

You can get the previous bar value ordering by id, (if you don't set an order, what is the previous row?)

SELECT
    id,
    bar,
    LAG(bar) OVER (ORDER BY id)
FROM
    t;

id | bar |  lag
-: | --: | ---:
 1 |   7 | null  --<< Note the NULL value
 2 |   8 |    7
 3 |  30 |    8
 4 |  25 |   30
 5 |  24 |   25
 6 |  24 |   24
 7 |  35 |   24
 8 |  40 |   35

Then simply use a conditional statement IF or CASE

SELECT
    id,
    bar,
    CASE 
        WHEN LAG(bar) OVER (ORDER BY id) < bar THEN 'Lower'
        WHEN LAG(bar) OVER (ORDER BY id) > bar THEN 'Higher'
        WHEN LAG(bar) OVER (ORDER BY id) = bar THEN 'Equal'
        ELSE 'Nothing to compare'
    END As Previous
FROM
    t;

id | bar | previous          
-: | --: | :-----------------
 1 |   7 | Nothing to compare
 2 |   8 | Lower             
 3 |  30 | Lower             
 4 |  25 | Higher            
 5 |  24 | Higher            
 6 |  24 | Equal             
 7 |  35 | Lower             
 8 |  40 | Lower            

db<>fiddle here

1

You can write stored procedures in plpgsql to get access to procedural behaviour in SQL, but you will probably get the best use of PostgreSQL if you come to terms with the set-based relational agebra which is the foundation of SQL.

First of all, you have to understand that a table (a.k.a. relation) is fundamentally an unordered set of tuples/records, not an ordered sequence. Order is a property of your query, through ORDER BY, not of the table as such. At any given time, it's obviously stored in some kind of order in memory and/or disk, but you can't rely on that order to be maintained.

For some plpgsql, see e.g:

https://carto.com/help/working-with-data/sql-stored-procedures/ https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

Not the answer you're looking for? Browse other questions tagged or ask your own question.