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)
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
How to get previous row value in present row in sql server
you can use String_agg function like below
create table t (id int, name nvarchar(max));
insert into t values (1 , 'abc'),(2, 'def'),(3,'xyz');
select t1.id,string_agg(t2.name,'') from t t1 left join t t2
on t1.id>=t2.id
group by t1.id
demo link
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 get previous row data in sql server
Use lag()
function
select *, lag(col3) over (partition by col1 order by col2) as col4
from table t;
However You can also use subquery
if your SQL
doesn't have LAG()
select *,
(select top 1 col3
from table
where col1 = t.col1 and col2 < t.col2
order by col2 desc
) as col4
from table t;
Get previous row value based on a timestamp for matching IDs
You need to PARTITION BY
the ID
column.
lag(pc.CountryArrival) over (PARTITION BY ID order by DateArrival) as DeparturePort
Subquery in access: how to get previous row calculated value
Use the query result in another query. Consider:
SELECT Data.*,
gross - Nz((SELECT Sum(x) AS SX FROM Data AS Dupe WHERE Dupe.Period_end<Data.Period_end ),0) AS AdjGross,
gross - (SELECT Sum(x) AS SX FROM Data AS Dupe WHERE Dupe.Period_end<=Data.Period_end) AS AdjNet
FROM Data;
Possible alternative is to build a report based on your query and use textbox RunningSum property.
How to get previous row in SELECT conditionally?
What you are looking for is the PARTITION BY clause in your OVER function:
select customer_id, merchandise_name,
purchase_date -
LAG(purchase_date) over (partition by customer_id, merchandise_type
order by purchase_date) as days
from Transactions
Without this clause you will get any previous value for the purchase_date
.
Related Topics
How to Query Between Two Dates Using MySQL
Difference Between Numeric, Float and Decimal in SQL Server
MySQL Error: Key Specification Without a Key Length
MySQL Insert Query Doesn't Work With Where Clause
How to Get List of Dates Between Two Dates in MySQL Select Query
MySQL Foreign Key Constraint Is Incorrectly Formed Error
How to Search (Case-Insensitive) in a Column Using Like Wildcard
Passing Multiple Values For a Single Parameter in Reporting Services
Check If Value Exists in Postgres Array
Including Parameters in Openquery
How to Convert Comma Separated Values to Rows in Oracle
If' in 'Select' Statement - Choose Output Value Based on Column Values
Why Would Someone Use Where 1=1 and ≪Conditions≫ in a SQL Clause
How to Split the Name String in MySQL
Why Isn't SQL Ansi-92 Standard Better Adopted Over Ansi-89