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

Mysql – Get latest query based on two values but but only if value 1 is newer than value 2

I have a table (audit) like so

id | id_audit | id_booking
--------------------------
8  |  12345  |  10000
7  |  34567  |  10000
6  |  25608  |  10000
5  |  25864  |  10000
4  |  12345  |  10001
3  |  34567  |  10001
2  |  25864  |  10001
1  |  25608  |  10001

I want to return rows, one for each id_booking group but only if id_audit=25608 is newer than id_audit=25864. Newer is a higher id.

So for the records above, it would return only

id | id_audit | id_booking
--------------------------
6  |  25608  |  10000

Is there an efficient query to solve this? The index on the table is id_booking, id_audit

I tried another query which works but it is inefficient and sometimes brings the server to a crawl. This forum will not let me post it due to error:
Your post appears to contain code that is not properly formatted as code. Please indent all code by 4 spaces using the code toolbar button or the CTRL+K keyboard shortcut

Tags:

2

Answers


  1. 0

    I’m not sure if I understood what you meant and since your code isn’t posted, this is my try so let me know if I’m on the right track:

    SELECT a1.*
FROM audit a1
JOIN audit a2 ON a1.id_booking = a2.id_booking
WHERE a1.id_audit = 25608
AND a2.id_audit = 25864
AND a1.id > a2.id;
    Login or Signup to reply.
  2. 0

    You can do:

    select n.*
from t o
join t n on n.id_booking = o.id_booking
        and n.id > o.id
        and n.id_audit = 25608
        and o.id_audit = 25864

    Result:

    id  id_audit  id_booking
--  --------  ----------
 6     25608       10000

    See running example at db<>fiddle.

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

Back To Top
Search