mysql join one to many last record

Question

I'm working on a query where I join two tables on two id's. There is a one to many relationship between the two tables and I want to get the last record from the second table.
I've put a example on SQLfiddle, here you can see that I get the first records of the second table:
- one
- uno
- primary

I want the last records based on time (or timemodified), being:
- two
- quatro
- trinary

The tables look something like this:

---------------------
|id1|id2|randomvalue|
---------------------
|55 |87 |nise       |
|55 |88 |nape       |
|55 |89 |nusq       |
---------------------

------------------------
|id1|id2|content  |time|
------------------------
|55 |87 |one      |111 |
|55 |87 |two      |128 |
|55 |88 |uno      |321 |
|55 |88 |dos      |322 |
|55 |88 |tres     |384 |
|55 |88 |quatro   |390 |
|55 |89 |primary  |730 |
|55 |89 |secundary|733 |
|55 |89 |trinary  |788 |
------------------------

The query I wrote to get the first records:

SELECT one.id1, one.id2, two.content
FROM table1 AS one
JOIN table2 AS two ON one.id1=two.id1 AND one.id2=two.id2
GROUP BY two.id2

I've tried grouping and/or ordering time but to no avail and here my knowledge of SQL ends.

(yes, I know this query seems useles because I'm not combining a lot of info but in my actual database there are a lot more columns (and rows))

Gordon Linoff · Accepted Answer · 2018-11-21 15:51:00Z

4

Use an additional group by to get the last record:

SELECT one.id1, one.id2, two.content
FROM table1 one JOIN
     table2 two
     ON one.id1 = two.id1 AND one.id2 = two.id2 JOIN
     (SELECT t.id2, MAX(time) as maxt
      FROM table2 t
      GROUP BY t.id2
     ) t
     ON two.id2 = t.id2 and two.time = maxt

edited Nov 21, 2018 at 15:51

answered Jun 5, 2015 at 10:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nhalink Over a year ago

This does work but table one is 3.8 million records and table two is 8.5 million records, I don't know if the database can handle this

Collectives™ on Stack Overflow

mysql join one to many last record

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related