CODEWITHSUNDEEP
phpmysqlsqldatabase
lrich
lrich

Reputation: 80

Joining most recent column entry to MySQL query result

I have a PHP/MySQL based solution for displaying an ID and the number of entries that ID has on a MySQL database table in the past day, and year to date. The information is simply output to a table.

We would also be able to quickly see the most recent value for a particular column associated with each ID on this same table.

The tables in use have the following descriptions:

TABLE: tags
id        int    auto_increment
Tag       int(8)
Timestamp timestamp
Battery   varchar(3)             // Status for rfid tags battery condition

TABLE: tag_vehicle
id        int     auto_increment
Tag       int(8)
VehicleId varchar(10)

And here is my MySQL query

SELECT tags.Tag, 
sum(CASE WHEN year(Timestamp) = year(CURDATE()) then 1 else 0 end) as ytd,
sum(CASE WHEN date(Timestamp) = date(CURDATE()) then 1 else 0 end) as today,
tag_vehicle.VehicleId, 
MAX(Timestamp) as latest
FROM tags
INNER JOIN tag_vehicle 
ON tags.Tag = tag_vehicle.Tag
GROUP BY VehicleNumber;

This generates a table with these columns

| Tag | ytd | today | VehicleId | Timestamp  |
|1234 | 300 |  12   |  BUS1234  | 2014-09-22 |

I'm simply looking to add to my table the latest value of Battery that matches each Tag. I've been trying all morning to produce this result but I haven't had any luck.

| Tag | ytd | today | VehicleId | Timestamp  | Battery |
|1234 | 300 |  12   |  BUS1234  | 2014-09-22 |   ok    |

I'm not an expert on MySQL and its starting to feel like the query is too messy. I'm having a hard time figuring out how to get this battery value.

How should I go about obtaining the latest entry for Battery and matching it to the right row? Is there some way to do it all in 1 (perhaps cleaner) query, or should I make 2 queries and match Battery columns to Tag columns?

Upvotes: 1

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You should be able to do this with the substring_index()/group_concat() trick:

SELECT t.tag,
       sum(CASE WHEN year(Timestamp) = year(CURDATE()) then 1 else 0 end) as ytd,
       sum(CASE WHEN date(Timestamp) = date(CURDATE()) then 1 else 0 end) as today,
       tv.VehicleId, 
       MAX(Timestamp) as latest,
       substring_index(group_concat(t.battery order by t.timestamp desc), ',', 1)
FROM tags t INNER JOIN
     tag_vehicle  tv
     ON t.Tag = tv.Tag
GROUP BY VehicleNumber;

The tag column doesn't look right because it comes from an indeterminate row.

Upvotes: 2

Related Questions