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

MYSQL select row before as previos data

This the example of the table name is merchant_point_log :

id    created_date   merchant_id   point_value   recent_point   status
 1    2022-01-02           1           5000           5000        earn 
 2    2022-01-02           2           3000           3000        earn
 3    2022-01-02           1           3000           3000        redeem

i would like to show logging like with previous recent point in one row like :

id    created_date  merchant_id   status   previous_point   point_value   recent_point   
 1    2022-01-02         1         in           0             5000           5000        
 2    2022-01-02         1         out         5000           3000           2000        
 3    2022-01-02         2         in          0              3000           3000

how do i return previos_point column from before row of selected data ?

i already tried this query but still doesn’t work as expected :

select
mpl.id 
, mpl2.recent_point as previous_point
, mpl.point_value
, mpl.recent_point
from merchant_point_log mpl
left join merchant_point_log mpl2 on mpl.created_date = adddate(mpl2.created_date, 1)
order by mpl.id asc
;

the result is previous point dont return as expected it always repeat same value. I use mysql version 5.7

Tags:

2

Answers


  1. 0

    We use lag to get previous_point and coalesce to put it as 0 in case previous_point is null.

    select id 
      ,created_date
      ,case status when 'earn' then 'in' else 'out' end            as status
      ,coalesce(lag(recent_point) over (order by created_date), 0) as previous_point
      ,point_value
      ,recent_point
from   t
    id created_date status previous_point point_value recent_point
    1 2022-01-02 00:00:00 in 0 2000 2000
    2 2022-01-02 00:00:00 in 2000 5000 7000
    3 2022-02-02 00:00:00 out 7000 3000 4000

    Fiddle

    Login or Signup to reply.
  2. 0

    Here’s an answer with left join for older versions where window functions are not an option.

    select   t.id 
        ,t.created_date
        ,case t.status when 'earn' then 'in' else 'out' end as status
        ,coalesce(t2.recent_point, 0)                       as previous_point
        ,t.point_value
        ,t.recent_point
from     t left join t t2 on t2.id = t.id-1
order by t.id
    id created_date status previous_point point_value recent_point
    1 2022-01-02 00:00:00 in 0 2000 2000
    2 2022-01-02 00:00:00 in 2000 5000 7000
    3 2022-02-02 00:00:00 out 7000 3000 4000

    Fiddle

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

Back To Top
Search