0

I"m on Mysql 5.6 so window functions aren't available...

I need to get data on a customerid level, but since this is on a transactional table, I want just the latest record which is denoted by the highest value of recordid (it's an autoincrement field). Think of the grain as this:

recordid | customerid | attribute1 | updatetime

I was thinking of doing this, but was wondering if there's a better way

select
  mytable.customerid,
  attribute1
from mytable
inner join (
  select 
    max(recordid) as maxid,
    customerid
  from mytable) as maxed
  on mytable.recordid = maxed.maxid
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
simplycoding
  • 2,770
  • 9
  • 46
  • 91
  • 1
    You don't need to select customerid in the subquery. You can also do a `WHERE recordid = (SELECT MAX(recordid) FROM mytable)` instead of a join. – clinomaniac Feb 21 '18 at 21:47
  • 1
    Unless you need to know the row with the max recordid *per customer*, in which case you should join the subquery to the out query on the two columns, maxid and customerid. Anyway, if this is the sort of problem you're trying to solve, follow the [tag:greatest-n-per-group] tag, there are many answers on Stack Overflow. – Bill Karwin Feb 21 '18 at 22:20

0 Answers0