Menu
How to get a value from previous result row of a SELECT statement
If we have a table called cardevent and has rows [ID(int) , Value(Money) ] and we have some rows in it, for example
ID --Value
1------70
1------90
2------100
2------150
2------300
3------150
3------200
3-----250
3-----280
so on...
How to make one Query that get each row ID,Value and the previous Row Value in which data appear as follow
ID --- Value ---Prev_Value
1 ----- 70 ---------- 0
1 ----- 90 ---------- 70
2 ----- 100 -------- 90
2 ------150 -------- 100
2 ------300 -------- 150
3 ----- 150 -------- 300
3 ----- 200 -------- 150
3 ---- 250 -------- 200
3 ---- 280 -------- 250
so on.
So can anyone help me to get the best solution for such a problem ?
Need Query Help
940
SQL Server LAG() is a window function that provides access to a row at a specified physical offset which comes before the current row. In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on.
You can use UNION to get the previous and next record in MySQL. Insert some records in the table using insert command. Display all records from the table using select statement.
LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row. Transact-SQL Syntax Conventions (Transact-SQL)
METHOD 1 : Using LIMIT clause in descending orderof specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending. Since Employee table has IDs, we will perform ORDER BY ID in our query.
SELECT t.*,
LAG(t.Value) OVER (ORDER BY t.ID)
FROM table AS t
This should work. The Lag function gets the previous row value for a specific column. I think this is what you want here.
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
