CODEWITHSUNDEEP
mysql
martincarlin87
martincarlin87

Reputation: 11052

MySQL - Group By Latest and Join First Instance

I've tried a few things but I've ended up confusing myself.

What I am trying to do is find the most recent records from a table and left join the first after a certain date.

An example might be

id | acct_no | created_at          | some_other_column
 1 |  A0001  | 2017-05-21 00:00:00 | x
 2 |  A0001  | 2017-05-22 00:00:00 | y
 3 |  A0001  | 2017-05-22 00:00:00 | z

So ideally what I'd like is to find the latest record of each acct_no sorted by created_at DESC so that the results are grouped by unique account numbers, so from the above record it would be 3, but obviously there would be multiple different account numbers with records for different days.

Then, what I am trying to achieve is to join on the same table and find the first record with the same account number after a certain date.

For example, record 1 would be returned for a query joining on acct_no A0001 after or equal to 2017-05-21 00:00:00 because it is the first result after/equal to that date, so these are sorted by created_at ASC AND created_at >= "2017-05-21 00:00:00" (and possibly AND id != latest.id.

It seems quite straight forward but I just can't get it to work.

I only have my most recent attempt after discarding multiple different queries.

Here I am trying to solve the first part which is to select the most recent of each account number:

SELECT latest.* FROM my_table latest
JOIN (SELECT acct_no, MAX(created_at) FROM my_table GROUP 
BY acct_no) latest2
ON latest.acct_no = latest2.acct_no

but that still returns all rows rather than the most recent of each.

I did have something using a join on a subquery but it took so long to run I quite it before it finished, but I have indexes on acct_no and created_at but I've also ran into other problems where columns in the select are not in the group by. I know this can be turned off but I'm trying to find a way to perform the query that doesn't require that.

Upvotes: 0

Views: 105

Answers (3)

krishn Patel
krishn Patel

Reputation: 2599

you need to get the id where max date is created. 

SELECT latest.* FROM my_table latest
join (SELECT max(id) as id FROM my_table GROUP 
BY acct_no where created_at = MAX(created_at)) latest2
ON latest.id = latest2.id

Upvotes: 0

Akhil
Akhil

Reputation: 2602

Trying a different approach. Not sure about the performance impact. But hoping that avoiding self join and group by would be better in terms of performance. 

SELECT * FROM (
SELECT mytable1.*, IF(@temp <> acct_no, 1, 0) selector, @temp := acct_no FROM `mytable1` 
JOIN (SELECT @temp := '') a
ORDER BY acct_no, created_at DESC , id DESC
) b WHERE selector = 1

Sql Fiddle

Upvotes: 0

dev8080
dev8080

Reputation: 4020

Just try a little edit to your initial query:

SELECT latest.* FROM my_table latest
join (SELECT acct_no, MAX(created_at) as max_time FROM my_table GROUP 
BY acct_no) latest2
ON latest.acct_no = latest2.acct_no AND latest.created_at = latest2.max_time

Upvotes: 1

Related Questions