Question 136801 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

5

Answers


  1. 0 
    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

    Login or Signup to reply.
  2. 0

    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:

    WITH min_max AS (
  SELECT item, date, min(tran_id) AS min_id, max(tran_id) AS max_id
    FROM my_table
   GROUP BY item, date
)
SELECT item, date, min_id, my_table1.quantity, max_id, my_table2.quantity
  FROM min_max
  JOIN my_table AS my_table1
    ON min_max.min_id = my_table1.tran_id
  JOIN my_table AS my_table2
    ON min_max.max_id = my_table2.tran_id

    If WITH syntax is not supported you can use FROM (SELECT ...) syntax

    Hope it helped

    Login or Signup to reply.
  3. 0

    Starting with version 5.5, this is a working solution for any version of mysql:

    select t.Item, t.Date, t.opening_transaction, op.Quantity as OpeningBal, t.closing_transaction, oc.Quantity as ClosingBal 
from (
  select Item, Date, min(transactionid) as opening_transaction,
                   max(transactionid) as closing_transaction
  from mytable
  group by item, Date
) t
inner join mytable op on op.transactionid = t.opening_transaction
inner join mytable oc on oc.transactionid = t.closing_transaction

    Demo here

    Login or Signup to reply.
  4. 0

    Using first_value and last value window functions, try the following:

    select Item, Date_, transactionid_1, OpeningBal, transactionid_2, ClosingBal
from
(
  select Item, Date_, 
   min(transactionid) over (partition by Item, Date_) transactionid_1,
   first_value(Quantity) over (partition by Item, Date_ order by transactionid) OpeningBal,
   max(transactionid) over (partition by Item, Date_) transactionid_2,
   last_value(Quantity) over (partition by Item, Date_ order by transactionid range between unbounded preceding and unbounded following) ClosingBal,
  transactionid
 from table_name
) t
where transactionid = transactionid_1
order by Item, Date_

    demo

    Login or Signup to reply.
  5. 0

    You can use window functions like so:

    select item, date, 
    transation_id opening_transaction_id, balance opening_quantity,
    closing_transaction_id, closing_quantity
from (
    select t.*, 
        max(transation_id)    over(partition by item, date) closing_transaction_id,
        first_value(quantity) over(partition by item, date order by transaction_id desc) closing_quantity,
        row_number()          over(partition by item, date order by transaction_id) rn
    from mytable t
) t
where rn = 1

    The subquery computes the date and the quantity of the last daily transaction , using max() and first_value() respectively. It also identifies the row that correspond to the first transaction, using row_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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search