CODEWITHSUNDEEP
mysqlsqljoin
nhalink
nhalink

Reputation: 513

mysql join one to many last record

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))

Upvotes: 6

Views: 4191

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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

Upvotes: 4

Related Questions