How to find first and last value of a column in SQL?

Question

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

Answer 1

SELECT item, `date`, 
       MAX(CASE WHEN first_last.opening = test.trasactionid THEN test.trasactionid END) OpeningId,
       MAX(CASE WHEN first_last.opening = test.trasactionid THEN quantity END) OpeningBal, 
       MAX(CASE WHEN first_last.closing = test.trasactionid THEN test.trasactionid END) ColsingId,
       MAX(CASE WHEN first_last.closing = test.trasactionid THEN quantity END) ClosingBal
FROM test
JOIN (
  SELECT item, `date`, MIN(trasactionid) opening, MAX(trasactionid) closing
  FROM test
  GROUP BY 1,2
) first_last USING (item, `date`)
GROUP BY 1,2

https://dbfiddle.uk/QQlpAnJt