MySQL: find max(created_at) that where another_column in (a,b,...)

Question

Context: I am working with mortgage information scraped from another page, so the data I am working with is potentially inconsistent.

The potentially-relevant columns: created_at, updated_at, type, mortgage_duration, (either 15 or 30 years).

Given a specific type that I am looking for, I need to find the most recent mortgage information for a mortgage_duration of 15 and a mortgage_duration of 30. I only need one of each, because that's all we're displaying.

The result would potentially look like:

+-----------+-----+--------+-------+---------------------+----------+
| rate_type | m_d | info1  | info2 | created/updated_at  | info3    |
+-----------+-----+--------+-------+---------------------+----------+
| type1     | 15  |  12345 | 11111 | 2017-09-20 21:44:49 |    09876 |
| type1     | 30  |  23456 | 22222 | 2017-09-20 21:44:49 |    12121 |
+-----------+-----+--------+-------+---------------------+----------+

The created/updated_at don't need to be the same between the 15- and 30-year, but they will much of the time.

EdmCoff · Accepted Answer · 2017-09-21T14:17:09.990

1

Perhaps not the most elegant solution, but this is readable to me.

 (SELECT *
 FROM table
 WHERE mortgage_duration=15
  and type='type1'
 ORDER BY created_at DESC
 LIMIT 1)
UNION ALL
 (SELECT *
 FROM table
 WHERE mortgage_duration=30
  and type='type1'
 ORDER BY created_at DESC
 LIMIT 1)

edited Sep 21 '17 at 14:17

answered Sep 20 '17 at 22:45

EdmCoff

3,506
1
9
9

I'm going to +1 this since it led to a solution that worked, but it isn't 100% correct. From the MySQL docs on unions: "To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT": https://dev.mysql.com/doc/refman/5.7/en/union.html I was also getting errors that got fixed when I specified table nicknames, so my statement looked like (SELECT * FROM table t WHERE t.mortgage_duration=15 ORDER BY created_at DESC LIMIT 1) UNION (SELECT * from table t1 ...) – Jon Ohliger Sep 20 '17 at 22:58
@JonOhliger Glad it helped. I've edited it for the correct syntax... sloppy on my part. – EdmCoff Sep 21 '17 at 14:18

MySQL: find max(created_at) that where another_column in (a,b,...)

1 Answers1