MySQL display rows from 2 tables with most recent data in table 2

Question

I have 2 tables to use, I want display the result depending on the recent data in the table2 where the owner in Table1 = 'ownerA': Table1 :

    # id,    name,           owner
________________________________________________
    19782, device1,        ownerA
    19783, device8,        ownerB
    19784, device2,        ownerA
    19785, device3,        ownerA

Table2 :

# nasid, sim, timestamp
_______________________________________
19782, 0, 2015-12-08 15:34:27
19782, 0, 2015-12-08 15:34:33
19772, 0, 2015-12-08 15:34:39
19752, 0, 2015-12-08 15:34:45
19783, 0, 2015-12-08 15:34:50
19712, 0, 2015-12-08 15:34:56
19783, 0, 2015-12-08 15:35:02
19782, 0, 2015-12-08 15:35:07
19784, 0, 2015-12-08 15:35:13
19784, 0, 2015-12-08 15:35:20

What I want to get in output :

# name, nasid, sim,     timestamp
_______________________________________
device8, 19783, 0, 2015-12-08 15:35:02
device1, 19782, 0, 2015-12-08 15:35:07
device2, 19784, 0, 2015-12-08 15:35:20

This is what I tried, :

select nasid, sim, MAX(timestamp) 
from Table1 T1,Table2 T2
where 
Table1.owner='ownerA' 
and
T2.timestamp = (SELECT MAX(T2_2.timestamp)
                    FROM Table2 T2_2
                    WHERE T2.sim=T2_2.sim)

This is my SHOw CREATE : Table1:

CREATE TABLE `Table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE latin1_general_ci DEFAULT NULL,
  `owner` varchar(128) COLLATE latin1_general_ci DEFAULT '',
  UNIQUE KEY `name` (`name`),
  KEY `id` (`id`),
  KEY `owner` (`owner`),
) ENGINE=InnoDB AUTO_INCREMENT=26696 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Table2:

CREATE TABLE `Table2` (
  `nasid` int(11) DEFAULT NULL,
  `sim` char(20) COLLATE latin1_general_ci DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  KEY `nasid` (`nasid`),
  KEY `timestamp` (`timestamp`),
  KEY `sim` (`sim`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Why is device8 in your expected output if the owner is 'B'? and your where clause has ownerA? — xQbert, Nov 17 '16 at 15:23

xQbert · Accepted Answer · 2016-11-17T15:29:36.653

1

Generally I generate a data set with the max values and the unique key then simply join it back to the base set containing the other data we need.

SELECT T1.name, T2.nasid, T2.sim, T2.timestamp
FROM Table1 T1
INNER JOIN table2 T2
 on T1.ID = T2.nasid
INNER JOIN (SELECT max(timestamp) mt, nasid 
            FROM table2 
            GROUP BY  nasid) A
   on A.MT = T2.TimeStamp
  and A.Nasid = T2.Nasid
WHERE T1.Owner = 'ownerA'

Well lets go back to your original query...

I think all you had wrong there was sim should be nasid. as sim doens't appear to be the key to the table. Only nasid and timestamp based on data presented would generate a unique key. on and you're missing the join between t1 and t2.

select name, nasid, sim, timestamp
from Table1 T1,Table2 T2
where Table1.owner='ownerA' 
and T1.Id = t2.nasid
and T2.timestamp = (SELECT MAX(T2_2.timestamp)
                    FROM Table2 T2_2
                    WHERE T2.nasid=T2_2.nasid)

edited Nov 17 '16 at 15:29

answered Nov 17 '16 at 15:07

xQbert

34,733
2
41
62

It works, but it takes a lot of time. my table is really huge. I think a lot of inner join make it a bit slow – user2997418 Nov 17 '16 at 15:15
Eliminate the where clause do you get results then? If so I probably have the casing on `ownerA` wrong. (it was `OwnerA`) – xQbert Nov 17 '16 at 15:20
Do you have indexes on T1.Owner, T1.Nasid and T2.Timestamp and Nasid? – xQbert Nov 17 '16 at 15:22
they are all key, MUL – user2997418 Nov 17 '16 at 15:24
Seems odd, I've used the above on tables having millions of records with proper indexes and have results in a few seconds. I revisited your original approach as something else to try but I don't see how we'll get much better performance; but it's a different approach. – xQbert Nov 17 '16 at 15:36

score 0 · Answer 2 · answered Nov 18 '16 at 10:13

0

After long time, This is what makes it faster :

SELECT name, nasid, sim,  max(timestamp)
FROM Table1, Table2
WHERE Owner = 'OwnerA'
and id = nasid 
GROUP BY  nasid

answered Nov 18 '16 at 10:13

user2997418

648
1
8
22

MySQL display rows from 2 tables with most recent data in table 2

2 Answers2