MySQL Forums
Forum List  »  General

Get latest query based on two values but but only if value 1 is newer than value 2
Posted by: Don Pro
Date: February 13, 2024 01:44PM

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

This works but is inefficient, takes long to run, and occasionally slows the server down whereby employees are affected.

SELECT a1.* FROM AUDIT a1
INNER JOIN
(
SELECT MAX(id) AS max_aud, id_audit, id_booking
FROM AUDIT
WHERE (id_audit='25608' OR id_audit='25864')
GROUP BY id_booking
) a2
ON a1.id_booking=a2.id_booking
AND a1.id=max_aud`
AND a1.id_audit='25608'

Options: ReplyQuote


Subject
Written By
Posted
Get latest query based on two values but but only if value 1 is newer than value 2
February 13, 2024 01:44PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.