MySQL 5 : using a LAG / LEAD request without LAG / LEAD functions

user_14720272 0
Viewed 58

I am having trouble with a SQL request on a very old version (MySQL 5). It seems my need could be easily answered with LAG and LEAD functions, but they do not exist before MySQL 8, so this is not an option...

So here is a simplified set of data :

CREATE TABLE Tickets (
    incrementalID integer,
    TicketNumber smallint,
    SupportGroup varchar(100)
);
 
INSERT INTO Tickets (incrementalID, TicketNumber, SupportGroup) VALUES 
(345678, 131, 'GSSbdd'),
(347681, 131, 'GSSmdw'),
(347682, 131, 'COPsn1'),
(347683, 131, 'COPsn1'),
(347684, 131, 'COPsn2'),
(342631, 198, 'VIReer'),
(347629, 227, 'LPOdfh'),
(350112, 299, 'COPmwn'),
(350113, 299, 'GSSgdf'),
(350119, 299, 'COPmwn'),
(346784, 714, 'LPOerz'),
(346871, 714, 'GSSwnt'),
(346872, 714, 'GSSunx'),
(346873, 714, 'GSSunx'),
(348931, 714, 'GSSwnt'),
(348941, 714, 'LPOefe'),
(401232, 714, 'LPOefe'),
(401233, 714, 'LPOefe'),
(412344, 714, 'LPOeze'),
(412345, 714, 'LPOeze'),
(416377, 714, 'GSSunx'),
(416378, 714, 'GSSmdw'),
(416379, 714, 'GSSgdf'),
(416380, 714, 'GSSgdf'),
(416381, 714, 'GSSgdf');

And I would need to SELECT only rows having the "SupportGroup" field changing from GSS% to COP% or COP% to GSS%, for identicals TicketsNumbers

So for example, for TicketNumber 131, I only want to SELECT the rows 2 and 3 because SupportGroup changed from GSS% (GSSmdw) to COP% (COPsn1).

I don't want to SELECT rows having a SupportGroup change between GSS% and another GSS% (like in Rows 1 et 2 by example, GSSbdd to GSSmdw)

So at the end, the expected result is :

incrementalID   TicketNumber    SupportGroup
347681          131             GSSmdw
347682          131             COPsn1
350112          299             COPmwn
350113          299             GSSgdf
350119          299             COPmwn

For information my data have to be ordered by TicketNumber then incrementalID, and I don't have writing rights on the database, so I can only do reading SQL requests.

row request select mysql
2 Answers

One method is a correlated subquery. You can use the overloaded having for filtering:

select t.*,
       (select t2.SupportGroup
        from tickets t2
        where t2.TicketNumber = t.TicketNumber and
              t2.incrementalID < t.incrementalID
        order by t2.incrementalID desc
        limit 1
       ) as prev_SupportGroup,
       (select t2.SupportGroup
        from tickets t2
        where t2.TicketNumber = t.TicketNumber and
              t2.incrementalID > t.incrementalID
        order by t2.incrementalID asc
        limit 1
       ) as next_SupportGroup
from tickets t
having (prev_SupportGroup like 'GSS%' and SupportGroup like 'COP%') or
       (SupportGroup like 'GSS%' and next_SupportGroup like 'COP%');
user_11440350 
SELECT *
FROM Tickets t1
JOIN Tickets t2 ON t1.TicketNumber = t2.TicketNumber
WHERE t1.incrementalID < t2.incrementalID
  AND NOT EXISTS ( SELECT NULL
                   FROM Tickets t3
                   WHERE t1.TicketNumber = t3.TicketNumber
                     AND t1.incrementalID < t3.incrementalID
                     AND t3.incrementalID < t2.incrementalID)
  AND (LEFT(t1.SupportGroup, 3), LEFT(t2.SupportGroup, 3)) IN (('COP', 'GSS'), ('GSS', 'COP'))

fiddle

user_101387340
Related