mysql Trying to select latest timestamp only

Question

I am trying to join 2 tables and select the latest timestamp only and the field values associated with that latest timestamp

Data tables:

device_count

id  device_name     platform    version     timestamp
1   abc             123         x           2013-12-30
2   abc             123         y           2014-01-01
3   def             345         x           2013-12-30

device_list

id      device_name     Network     Division    Market
2657    def             a           f           d
2656    abc             a           b           c

The desired result should be

device_name     platform    version     network     division    market  timestamp
abc             123         y           a           b           c       2014-01-01
def             345         x           a           f           d       2013-12-30

I have used the following query but I'm not getting the correct results

SELECT
device_count.device_name,
device_count.platform,
device_count.version,
Max(device_count.`timestamp`),
device_list.Network,
device_list.Division,
device_list.Market
FROM
device_count
INNER JOIN device_list ON device_count.device_name = device_list.Device_Name
GROUP BY
device_count.device_name,
device_count.platform,
device_count.version,
device_list.Network,
device_list.Division,
device_list.Market

It results in

device_name     platform    version     network     division    market  timestamp
abc             123         x           a           b           c       2013-12-30
abc             123         y           a           b           c       2014-01-01
def             345         x           a           f           d       2013-12-30

The first record should not be included. I only want the version and platform values from the latest timestamp (record id 2)

You are grouping by the `version` and `network` numbers, so you will get every one in the output. — Gordon Linoff, Feb 12 '14 at 00:27

RamblinRose · Answer 1 · 2014-02-12T22:57:09.273

0

try subquery:

SELECT
device_count.device_name,
device_count.platform,
device_count.version,
Max(device_count.`timestamp`),
device_list.Network,
device_list.Division,
device_list.Market
FROM
device_count DC
INNER JOIN device_list ON device_count.device_name = device_list.Device_Name
inner join 
    (select device_name, max(timestamp) timestamp
    from device_count
    group by device_name) as DC on DC.timestamp = device_count.timestamp and DC.device_name=device_count.device_name

edited Feb 12 '14 at 22:57

answered Feb 12 '14 at 00:41

RamblinRose

172
6

I did try to just use group by device_name and I get the correct latest timestamp but the version is incorrect. I get the version x from record 1 and timestamp from record 2 when all I want is record 2 and 3 – cdeniro Feb 12 '14 at 01:02
I edited the query, and changed it from a group by to a subquery. Try that out. – RamblinRose Feb 12 '14 at 02:45
I tried the subquery but its not working due to unknown column device_count.device_name. I tired to hack it together and now get subquery returns more than 1 row – cdeniro Feb 12 '14 at 15:18
I've made another edit, this one supplants the WHERE with an additional INNER JOIN to a subquery that aggregates the device_names with the highest timestamp. I tested this on SQL Server, and it might just work on mysql. – RamblinRose Feb 12 '14 at 17:01
I still have the same issue. Unknown column 'device_count.device_name' in 'field list'. Seems to be declaring the table as DC in the from statement is causing the problem – cdeniro Feb 12 '14 at 22:43
It may be that mysql wants "AS DC" to alias the inner join table. See new edit. – RamblinRose Feb 12 '14 at 22:57

score 0 · Answer 2 · answered Feb 12 '14 at 02:25

0

One way is to match to a subselect:

SELECT
  device_count.device_name,
  device_count.platform,
  device_count.version,
  device_count.`timestamp`,
  device_list.Network,
  device_list.Division,
  device_list.Market
FROM
  device_count
INNER JOIN 
  device_list 
ON device_count.device_name = device_list.Device_Name
WHERE device_count.`timestamp` = (
    SELECT MAX(dc2.`timestamp`)
    FROM
      device_count AS dc2
    WHERE
      dc2.device_name =  device_count.device_name
    )

answered Feb 12 '14 at 02:25

Turophile

3,367
1
13
21

Thanks Turophile. This does work however on the real data set it takes over 7 mins to complete. device_list contains ~2500 records and device_count contains ~40000. Any way to improve the performance? – cdeniro Feb 12 '14 at 15:06
Is there an index on `device_name` in both tables? – Turophile Feb 13 '14 at 01:34

mysql Trying to select latest timestamp only

2 Answers2