MySql: Select value of current and next row

Question

I am trying to learn of a better way in achieving the desired result of a select query - details below, thank you in advance.

MySQL version: 5.7

Table: id int(11) product_number int(8) service_group int (4) datetime datetime value int (6)

Indexes on all but value column.

MySql table has the following data:

id,product_number, service_group,datetime,value
1,1234,1,2022-02-10 00:00:00,0
2,1234,1,2022-02-10 00:01:30,25
3,1234,1,2022-02-10 00:02:30,11
4,1234,2,2022-02-10 01:00:30,0
5,1234,2,2022-02-10 01:01:30,65
6,1234,2,2022-02-10 01:02:30,55

In essence, the value for each product within the service group is wrongly recorded, and the correct value for the "current" row is actually recorded against the next row for the product within the same service group - correct output should look like this:

id,product_number, service_group,datetime,value
1,1234,1,2022-02-10 00:00:00,25
2,1234,1,2022-02-10 00:01:30,11
3,1234,1,2022-02-10 00:02:30,0
4,1234,2,2022-02-10 01:00:30,65
5,1234,2,2022-02-10 01:01:30,55
6,1234,2,2022-02-10 01:02:30,0

The below query is what seems to be hugely inefficient way of returning the correct results - what would be a better way to go about this in MySql? Thank you.

Select 
    a.id,
    a.product_number, 
    a.service_group,
    a.datetime,
    (
    Select b.value FROM products b
    Where b.product_number=a.product_number AND b.service_group=a.service_group
    AND b.datetime>a.datetime
    Order by b.datetime ASC
    Limit 1
    )
FROM products a```

Answer 1

I think what you need in THIS case is the Windows LEAD() function and can be found Here for example and clarification

In summary, LEAD() looks at the NEXT possible record for the given column in question, LAG() looks at the prior.

So in this example, I am asking for the LEAD() of the record (the next one in line) and getting the VALUE column of that record. The 1 represents how many records to skip ahead, in this case 1. The last parameter 0 is what should be returned if no such record exists.

The second half of the clause with the ORDER BY clause identifies how you want the records returned, in this case the datetime sequence.

I included both the value and the NEXTVALUE so you could see the results, but can remove the extra column once you see and understand how it works.

But since you have each service group to its own, and dont want to carry-over the value from another, you need the PARTITION clause as well. So I added that as an additional column... both so you can see how the results work with OR without it and the impact of the query you need.

select 
        t.id,
        t.product_number,
        t.service_group,
        t.datetime,
        t.value,
--        lead (t.value, 1, 0) 
--            over (order by t.datetime) as NextValue,
        -- without the ,1, 0 in the lead call as sample above
        -- you can see the NULLs are not getting the value
        -- from the next row when the service group changes.
        lead (t.value) 
            over ( PARTITION BY 
                        t.service_group 
                    order by 
                        t.datetime) as NextValuePerServiceGroup
    from 
        Tmp1 t
    order by
        t.service_group,
        t.datetime
 

Answer 2

If there's no skipped id (the number is in sequence) then you could probably use simple select like below

1.

Select 
    a.id,
    a.product_number, 
    a.service_group,
    a.datetime,
    (Select b.value FROM products b Where b.id = a.id+1)
FROM products a

Select 
    a.id,
    a.product_number, 
    a.service_group,
    a.datetime,
    b.value
FROM products a
INNER JOIN products b ON b.id = a.id+1

Note that both SQL 1 and 2 is assuming your ID is primary key as I see that's an incrementing value

Either way you need to run an explain query so you could analyze which one is the most efficient one

And more importantly I suggest to update it if it's "wrongly recorded", you should put the your service on maintenance mode and do update+fix on the data using query

Edit: based on your comment "Hi, Gunawan. Thank you for your suggestion. Unfortunately IDs will not be in sequences to support the proposed approach."

You could alter the subquery on (1) a bit to

Select b.value 
FROM products b 
Where b.id > a.id order by id asc limit 1

so it became

Select 
    a.id,
    a.product_number, 
    a.service_group,
    a.datetime,
    (Select b.value FROM products b Where b.id > a.id order by b.id asc limit 1)
FROM products a