0

I have the following table with some sample data.

Record_ID    Counter    Serial    Owner
1           0           AAA       Jack
2           1           AAA       Kevin
3           0           BBB       Jane
4           1           BBB       Wendy

Based on data similar to the above, I am trying to write a SQL query for MySQL that gets the record with the maximum Counter value per Serial number. The part I seem to be having trouble with is getting the query to get the last 50 unique serial numbers that were updated.

Below is the query I came up with so far based on this StackOverflow question.

SELECT * 
FROM   `history` his 
   INNER JOIN(SELECT serial, 
                     Max(counter) AS MaxCount 
              FROM   `tracking` 
              WHERE  serial IN (SELECT serial 
                                FROM   `history`) 
              GROUP  BY serial
              ORDER  BY record_id DESC) q 
           ON his.serial = q.serial 
              AND his.counter = q.maxcount 
LIMIT  0, 50
Community
  • 1
  • 1
musubi
  • 1,256
  • 3
  • 14
  • 22
  • Where is the point of the Where clause in your subquery? – Limey Apr 15 '13 at 20:15
  • I meant to remove it initially, but the `WHERE serial IN (SELECT serial FROM \`history\`` was where I was trying to create another subquery to select the last 50 unique serial numbers that were updated. – musubi Apr 15 '13 at 20:31
  • Doesn't MySQL support the Top 50 command? – Limey Apr 15 '13 at 20:32
  • In MySQL, the equivalent of the `TOP` Clause is `LIMIT`. Within the subquery you asked about in the above comment, I tried LIMIT 0, 50, but `LIMIT` can't be used in subqueries. – musubi Apr 15 '13 at 20:37

1 Answers1

1

It looks like a classic problem, which can be solved by something like this:

select his.Record_ID, his.Counter, his.Serial, his.Owner
from History his
inner join(
    select Serial, max(Counter) Counter
    from History
    group by Serial
) ss on his.Serial = ss.Serial and his.Counter = ss.Counter

If you are to have specific filters on your data set, you should apply the said filters in the sub-query.

Another source with more explanation on the problem here: SQL Select only rows with Max Value on a Column

Community
  • 1
  • 1
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
  • Does this solution solve the problem of returning just 50 entries with the last updated Serials?.. – zavg Apr 15 '13 at 20:16
  • @AdrianCarneiro Thanks for your answer. However, it doesn't seem to return the last 50 unique serials that were updated. – musubi Apr 15 '13 at 21:30