Really struggling matching up other people examples on this one, so wonder if someone would be good enough to point me in the right direction....
What I have are 2 tables in MySQL.
Tags
tagid, status, lot, lat, long, createuser, timestamp
Users
userid, first, surname
My process just adds rows to the Tags table, for the tagid scanned so there could be many rows with the same tagid but each row will have different info depending on the user, with each row having the timestamp of when it happened.
The ask is that I would like to list the latest record for each tagid, but I would like to exclude anything with a Tags.status of 'store' and enumerate the Tags.createuser to the name of the Users.userid
I just cant figure out how to get the last timestamp, as well as do the NOT statement, given there could be a situation like below.
tagid, status, lot, lat, long, createuser, timestamp
1000001, live, 1, xxxx, yyyy, 1, 2020-10-20 12:00
1000001, store, 1, xxxx, yyyy, 1, 2020-10-20 12:10
1000002, live, 1, xxxx, yyyy, 2, 2020-10-20 11:00
User 2 = Joe Bloggs
So the only thing I want returned is below because the last record for 1000001 was 'store'
1000002, live, 1, xxxx, yyyy, Joe Bloggs, 2020-10-20 11:00
You want the latest record per tag, along with the associated user name - if and only if the status of that tag is "live".
You can use row_number() and filtering:
select t.*, u.surname
from users u
inner join (
select t.*, row_number() over(partition by tagid order by timestamp desc) rn
from tags
) t on t.createduser = u.userid
where t.rn = 1 and t.status = 'live'
This requires MySQL 8.0. In earlier versions, one option uses a correlated subquery for filtering:
select t.*, u.surname
from users u
inner join tags t on t.createduser = u.userid
where t.status = 'live' and t.timestamp = (
select max(t1.timestamp) from tags t1 where t1.tagid = t.tagid
)