HobbyCoder HobbyCoder - 4 years ago 140
SQL Question

MySQL Query: Select latest record where column contains certain criteria

I have a table that I need to query to obtain the most recent record in which the description contains certain data. The table columns contain (in part) the following:

+-----------+------------+-------------------+
| AccountID | Date | Description |
+-----------+------------+-------------------+
| 125060 | 2006-02-11 | Red Apple |
| 125060 | 2007-03-23 | Yellow Banana |
| 125060 | 2009-04-03 | Yellow Apple |
| 125687 | 2006-03-10 | Red Apple |
| 139554 | 2007-06-29 | Orange Orange |
| 139554 | 2009-07-24 | Green Apple |
| 145227 | 2008-11-22 | Green Pear |
| 145227 | 2012-04-16 | Yellow Grapefruit |
| 154679 | 2014-05-22 | Purple Grapes |
| 163751 | 2012-02-11 | Green Apple |
| ... | ... | ... |
+-----------+------------+-------------------+


(There are a few more columns, and hundreds of thousands of records, but this is all I am interested in at the moment)

For this example, I want the most recent record for a subset of AccountIDs that contained "Apple." The results I am looking for are:

+-----------+------------+--------------+
| AccountID | Date | Description |
+-----------+------------+--------------+
| 125060 | 2009-04-03 | Yellow Apple |
| 125687 | 2006-03-10 | Red Apple |
| 139554 | 2009-07-24 | Green Apple |
+-----------+------------+--------------+


The current query I am using is:

SELECT AccountID, max(Date), Description
FROM products
WHERE Description like "%Apple%" and (AccountID=125060 or AccountID=125687 or AccountID=139554)
GROUP BY AccountID;


Unfortunately, the results are yielding something to the tune of:

+-----------+------------+-------------------+
| AccountID | Date | Description |
+-----------+------------+-------------------+
| 125060 | 2009-04-03 | Red Apple |
| 125687 | 2006-03-10 | Red Apple |
| 139554 | 2009-07-24 | Green Apple |
+-----------+------------+-------------------+


where the AccountID is grouping properly, and the appropriate (most recent) date is being selected, but the Description is still returning the FIRST description that matches the WHERE/like clause... and NOT the description that correlates to the record with the selected date.

I've never seen anything like this before. Is this something I am doing wrong? I don't have a ton of experience with advanced MySQL queries, but would this be better suited for a left join or inner join on a subquery?

I thought about first using a subquery to pull all records that contain the desired text within the description, and then querying that subquery to select/group by most recent, but didn't know if that would be necessary or not.

Thanks so much in advance for the help!

Answer Source

In your query nothing guarantees that mysql will select those description fields that have the max(date) value. Actually, your version is against the mysql standard and works in mysql under certain configuration settings only.

The solution is to get the max dates by account ids where the description matches the criteria in a subquery and join this back to the table itself using account id and max date:

SELECT p.AccountID, p.Date, p.Description
FROM products p
INNER JOIN (SELECT AccountID, max(Date) as maxdate
FROM products
WHERE Description like "%Apple%" and (AccountID=125060 or AccountID=125687 or AccountID=139554)
GROUP BY AccountID) t
ON p.AccountID=t.AccountID and p.Date=t.maxdate
WHERE Description like "%Apple%";

UPDATE

Mysql v4.0 does not support subqueries, therefore the above method is not applicable. You can still use a left join approach, where you self join the products table and use the is null expression to find those dates for which larger dates do not belong to:

select p1.*
from products p1
left join products p2
on p1.accountid=p2.accountid and p1.date<p2.date
where Description like "%Apple%" and p2.date is null;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download