MYSQL: select latest record only (on left join table)

Question

I have 2 tables:

Table1:

ID | Mobile Number | Name | Ordered Product| Order Date

Table2:

ID(foreign_key can be inserted multipletimes in this table) |Contacted_for | Time(timestamp)

I need a query to display all the data in Table1 and if the ID is present in Table 2, I need to display the last inserted record on Table2(with time) of that ID

My query is

select a.* , b.* FROM table1 a LEFT JOIN table2 b ON a.ID=b.ID GROUP BY a.ID ORDER BY b.Time DESC

Here in my query when I remove Group By a.ID, it works but shows all results. But I want to show final record of table2 only(no duplicate ID records)

Thanks in advance

Your question is so popular on SO it even has its own tag: [`greatest-n-per-group`](http://stackoverflow.com/questions/tagged/greatest-n-per-group). — axiac, Jan 16 '15 at 10:14

score 18 · Accepted Answer · answered Jan 16 '15 at 09:40

18

You'll need some subquery's for that:

SELECT
    a.*, b.*
FROM
    table1 a
LEFT JOIN
    (SELECT c.id, d.contacted_for, c.time
     FROM
         (SELECT
            id,
            MAX(time) time
         FROM
             table2
         GROUP BY id
         ) c
     JOIN
         table2 d
         ON c.id = d.id AND d.time = c.time
     ) b
     ON a.id = b.id

answered Jan 16 '15 at 09:40

Gervs

1,397
9
8

1

This is the way to go. Unfortunately in MySQL on a left join with "group by "it is undefined which of the sub-table records you get, and an order by clause doesn't change this. So you need to have a sub query. – Tuncay Göncüoğlu Jan 16 '15 at 09:57

score 7 · Answer 2 · edited May 28 '19 at 03:36

7

You can also try this way.

Select * from table1 a left join
(Select * from table2 where id in (select max(id) from table2 group by id) ) b on a.id=b.id

edited May 28 '19 at 03:36

Til

5,150
13
26
34

answered May 28 '19 at 03:16

user11069271

109
2
6

score -1 · Answer 3 · answered Jan 16 '15 at 09:06

-1

The best way to do it is to have CreatedAt and ModifiedAt fields in every table in database. Then you just add ORDER BY CreatedAd LIMIT 1. Not sure if your Time is what I mean.

What you also have is ID. Now, if ID is AutoIncremental then job should be easy. Use ORDER BY id DESC LIMIT 1

answered Jan 16 '15 at 09:06

Forien

2,712
2
13
30

1

if i add limit 1 it will display 1 data only.. I said I need to display all the data in table1.. Thanks for your reply.. – Pradeep Kumar Jan 16 '15 at 09:09

Pankaj katiyar · Answer 4 · 2015-01-16T09:49:57.440

-1

Try this ... I hope it may works

select a.* , b1.* 
FROM table1 a 
LEFT JOIN table2 b1
ON ( a.ID=b1.ID)  
LEFT JOIN table2 b2
ON ( a.ID=b1.ID)  AND 
(b1.Time < b2.Time OR b1.Time = b2.Time AND b1.ID < b2.ID)
WHERE b2.ID IS NULL
GROUP BY a.ID ORDER BY b1.Time DESC

edited Jan 16 '15 at 09:49

answered Jan 16 '15 at 09:16

Pankaj katiyar

464
10
26

"I hope" ?? No it won't work, you have some serious errors in the query, – Gervs Jan 16 '15 at 09:33
The query will work, but is not an answer to the question., because it will not display all the data of table 1. – Gervs Jan 16 '15 at 11:11
No it doesn't, OP wants ALL data in table1 and if the ID is present table2 then the most recent entry. If the ID isn't present in table2 b2.ID will be NULL and that's the where condition so it will be excluded. – Gervs Jan 19 '15 at 07:12

MYSQL: select latest record only (on left join table)

4 Answers4

Linked