How to Get a Value from Previous Result Row of a Select Statement

Is there a way to access the previous row value in a SELECT statement?

SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

If you know how to order things but not how to get the previous value given the current one (EG, you want to order alphabetically) then I don't know of a way to do that in standard SQL, but most SQL implementations will have extensions to do it.

Here is a way for SQL server that works if you can order rows such that each one is distinct:

select  rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2 
where t1.Rank = t2.Rank - 1

drop table temp1

If you need to break ties, you can add as many columns as necessary to the ORDER BY.

How to get previous row value

You would have to join the table with itself, I'm not sure if this is 100% legitimate SQL, but I have no SQL-Server to try this at the moment, but try this:

SELECT (ID, Value) from table as table1
inner join table as table2
on table1.ID = (table2.ID -1)

How to get a value from previous result row of a SELECT statement?

This sql should perform better then the one you have above, although these type of queries tend to be a little performance intensive... so anything you can put in them to limit the size of the dataset you are looking at will help tremendously. For example if you are looking at a specific date range, put that in.

SELECT followup.value, 
    ( SELECT TOP 1 f1.VALUE 
      FROM followup as f1 
      WHERE f1.id<followup.id 
      ORDER BY f1.id DESC
    ) AS Prev_Value
FROM followup

HTH

Sql query to identify running previous row value and do some computations

Updated with SQL for MySQL 8.0 and MariaDB 10.5

I couldn't guess your database, nor did any support your CREATE TABLE statement.

Since you tagged MySQL, I decided to just make the necessary changes to run with that database (and MariaDB).

One problem in your SQL is the standard and most databases do not support accessing a derived column in the same SELECT list. Normally, we would need to place the expression in a derived table or CTE term and then refer to the derived column in the outer (or subsequent) query expression.

An alternative is to use the original expression instead of the derived column name, as needed.

Here's an example of your SQL in MySQL 8.0 and MariaDB 10.5 using this approach (updated to use recursion):

SQL for MySQL/MariaDB:

WITH RECURSIVE data1 ( original_date, priority, seq ) AS (
          SELECT t.*, ROW_NUMBER() OVER (ORDER BY original_date) FROM test_sev AS t
     )
   , data0 ( original_date, prev_original_date, priority, new_original_date, seq ) AS (
          SELECT original_date, CAST(null AS DATETIME), priority, original_date, seq FROM data1 WHERE seq = 1 UNION ALL
          SELECT d1.original_date
               , d0.new_original_date
               , d1.priority
               , CASE
                    WHEN d1.priority > 2
                     AND TIMESTAMPDIFF(minute, d0.new_original_date, d1.original_date) < 4
                    THEN d0.new_original_date + INTERVAL '4' MINUTE
                    ELSE d1.original_date
                 END AS new_orig
               , d1.seq
            FROM data1 AS d1
            JOIN data0 AS d0
              ON d1.seq = d0.seq+1
     )
SELECT * FROM data0
;

Result:

+---------------------+---------------------+----------+---------------------+------+
| original_date       | prev_original_date  | priority | new_original_date   | seq  |
+---------------------+---------------------+----------+---------------------+------+
| 2021-07-29 14:45:00 | NULL                | 2        | 2021-07-29 14:45:00 |    1 |
| 2021-07-29 14:46:41 | 2021-07-29 14:45:00 | 3        | 2021-07-29 14:49:00 |    2 |
| 2021-07-29 14:47:13 | 2021-07-29 14:49:00 | 4        | 2021-07-29 14:53:00 |    3 |
| 2021-07-29 14:51:38 | 2021-07-29 14:53:00 | 3        | 2021-07-29 14:57:00 |    4 |
| 2021-07-29 14:51:46 | 2021-07-29 14:57:00 | 4        | 2021-07-29 15:01:00 |    5 |
| 2021-07-29 15:05:50 | 2021-07-29 15:01:00 | 1        | 2021-07-29 15:05:50 |    6 |
| 2021-07-29 15:13:50 | 2021-07-29 15:05:50 | 4        | 2021-07-29 15:13:50 |    7 |
+---------------------+---------------------+----------+---------------------+------+

Working Test Case for MySQL and MariaDB

How to use previous result that is made with previous row value in current row?

You could use the MODEL clause:

WITH A ( name, num1 ) AS (
  SELECT 'FIRST',   1 FROM DUAL UNION ALL
  SELECT 'SECOND',  3 FROM DUAL UNION ALL
  SELECT 'THIRD',   6 FROM DUAL UNION ALL
  SELECT 'FOURTH', 10 FROM DUAL UNION ALL
  SELECT 'FIFTH',   2 FROM DUAL
)
SELECT name, num1, num2
FROM   a
MODEL
  DIMENSION BY (ROWNUM AS rn)
  MEASURES ( name, num1, 2 AS num2)
  RULES (
    num2[rn>1] = num1[cv(rn)] * num2[cv(rn)-1]
  )

Which outputs:

NAME   | NUM1 | NUM2
:----- | ---: | ---:
FIRST  |    1 |    2
SECOND |    3 |    6
THIRD  |    6 |   36
FOURTH |   10 |  360
FIFTH  |    2 |  720

db<>fiddle here

Use a previous row value on where clause

You need to use CTE or subquery to analize (you cannot use window functions directly in WHERE clause) and then filter it.

CTE example:

WITH ExTable AS (SELECT 
  dhRegistro, 
  marcha, 
  LAG(marcha) OVER (ORDER BY dhRegistro) as lastMarcha
FROM Table)

SELECT dhRegistro, marcha
FROM ExTable
WHERE marcha = 0 AND lastMarcha = 1

---

Related Topics