I have a table having below schema
Item:Varchar
Date:Date
Quantity:Float
trasactionid:int
Sample Data:
Item | Date | Quantity | transactionid |
---|---|---|---|
Part1 | 01-01-2023 | 10 | 3 |
Part1 | 01-01-2023 | 15 | 5 |
Part1 | 01-01-2023 | 17 | 2 |
Part1 | 01-01-2023 | 13 | 6 |
Part1 | 02-01-2023 | 13 | 7 |
Part1 | 02-01-2023 | 1 | 8 |
Part1 | 02-01-2023 | 22 | 10 |
Part1 | 02-01-2023 | 5 | 12 |
I need to sort the data by transaction id and then find the unique part and date and Qty from first row of a group as Opening Balance and from last row as closing balance.
Required Output
Item | Date | transactionid | OpeningBal | transactionid | ClosingBal |
---|---|---|---|---|---|
Part1 | 01-01-2023 | 2 | 17 | 6 | 13 |
Part1 | 02-01-2023 | 7 | 13 | 12 | 5 |
I tried to use window funtion first_value and last value but unable to achieve the result
5
Answers
https://dbfiddle.uk/QQlpAnJt
I am not sure about mySql syntax but many SQL engines supports
WITH
syntax and I think it will be very easy for you to translate. This is a way to resolve your problem:If
WITH
syntax is not supported you can useFROM (SELECT ...)
syntaxHope it helped
Starting with version 5.5, this is a working solution for any version of
mysql
:Demo here
Using
first_value
andlast value
window functions, try the following:demo
You can use window functions like so:
The subquery computes the date and the quantity of the last daily transaction , using
max()
andfirst_value()
respectively. It also identifies the row that correspond to the first transaction, usingrow_number()
. Then, the outer query just filters on the top record per group, which already contains the information we need.The upside of this method is that it avoids aggregation and self-joins (which are less efficient in most cases) – and it has as few window function calls as possible.