MySQL Query to return row when column value changes and matches search parameters

Question

I need to write a MySQL query that will search through the version history for each lead_id and return the row for the earliest version where the lead type is like "%reservation%" and the Employee name is like "%Call Center%".

The idea is to find reservations created by the call center agents by returning the row for the version in which the reservation was created.

I've researched the other questions that are similar to this one but none of them seem to have my specific criteria. I'm not sure where to start. Assuming this would be some sort of subquery?

Here's an example of my data:

+---------+---------+-------------+----------------------------+-------------------+
| lead_id | version | lead_typeID |         Lead Type          |   Employee Name   |
+---------+---------+-------------+----------------------------+-------------------+
| 3576109 |       1 |        2552 | Online Inquiry             | Call Center - Joe |
| 3576109 |       2 |        2552 | Online Inquiry             | George            |
| 3576109 |       3 |        2552 | Online Inquiry             | Lisa              |
| 3576109 |       4 |        2552 | Online Inquiry             | Call Center - Bob |
| 3576109 |       5 |        2552 | Online Inquiry             | John              |
| 3576109 |       6 |        2552 | Online Inquiry             | George            |
| 3576109 |       7 |        2552 | Online Inquiry             | George            |
| 3576109 |       8 |        2552 | Online Inquiry             | John              |
| 3576114 |       1 |        2553 | Online Reservation         | Call Center - Joe |
| 3576114 |       2 |        2553 | Online Reservation         | Call Center - Bob |
| 3576142 |       1 |        4866 | Sales Center - Reservation | John              |
| 3576142 |       2 |        4866 | Sales Center - Reservation | Call Center - Joe |
| 3576160 |       1 |        2553 | Online Reservation         | Lisa              |
| 3576160 |       2 |        2553 | Online Reservation         | Call Center - Joe |
| 3576160 |       3 |        2553 | Online Reservation         | George            |
| 3576160 |       4 |        2553 | Online Reservation         | Lisa              |
| 3576164 |       1 |        4865 | Sales Center - Inquiry     | Call Center - Bob |
| 3576164 |       2 |        4865 | Sales Center - Inquiry     | John              |
| 3576164 |       3 |        4865 | Sales Center - Inquiry     | George            |
| 3576164 |       4 |        4865 | Sales Center - Inquiry     | George            |
| 3576193 |       1 |        2553 | Online Reservation         | John              |
| 3576193 |       2 |        2553 | Online Reservation         | Call Center - Joe |
| 3576248 |       1 |        2553 | Online Reservation         | Call Center - Bob |
| 3576248 |       2 |        2553 | Online Reservation         | John              |
| 3576261 |       1 |        2552 | Online Inquiry             | Call Center - Joe |
| 3576261 |       2 |        2552 | Online Inquiry             | Lisa              |
| 3576261 |       3 |        2552 | Online Inquiry             | Call Center - Joe |
| 3576263 |       1 |        4865 | Sales Center - Inquiry     | George            |
| 3576263 |       2 |        4865 | Sales Center - Inquiry     | Lisa              |
| 3576263 |       3 |        4865 | Sales Center - Inquiry     | Call Center - Bob |
| 3576263 |       4 |        4356 | Site - Reservation         | Call Center - Bob |
| 3576289 |       1 |        4865 | Sales Center - Inquiry     | George            |
| 3576289 |       2 |        4865 | Sales Center - Inquiry     | George            |
| 3576310 |       1 |        4867 | Sales Center - Lost        | John              |
| 3576310 |       2 |        4867 | Sales Center - Lost        | Call Center - Joe |
| 3576310 |       3 |        4867 | Sales Center - Lost        | Call Center - Bob |
+---------+---------+-------------+----------------------------+-------------------+

score 2 · Answer 1 · answered Aug 08 '19 at 21:06

It seems like (leadid, version) is unique. If so you can INNER JOIN an aggregation that gets the minimum version per lead ID.

SELECT t.*
       FROM elbat t
            INNER JOIN (SELECT leadid,
                               min(version) version
                               WHERE `Lead Type` LIKE '%reservation%'
                                     AND `Employee Name` LIKE '%Call Center%'
                               GROUP BY leadid) x
                       ON t.leadid = x.leadid
                          AND t.version = x.version;

score 0 · Answer 2 · answered Aug 08 '19 at 21:06

0

SELECT lead_id, MIN(version)
FROM WhateverThisTableIsCalled
WHERE `Lead Type` LIKE '%reservation%'
AND `Employee Name` LIKE '%call center%'
GROUP BY lead_id

I think this is right if I understand your question correctly.

answered Aug 08 '19 at 21:06

ewhiting

244
1
10

The question says to return the whole row, not just the version. – Barmar Aug 08 '19 at 21:12

MySQL Query to return row when column value changes and matches search parameters

2 Answers2