SQL Group By question - choosing which row gets grouped

Question

I have a table that tracks serial numbers and the number of licenses associated with said serial number. A client can re-license a box, increasing or decreasing the number of users, and they are only billed for the changed amount.

The following table structure exists:

id - Auto Incrementing ID
serial - The serial number
numusers - the number of licenses
date - The date that the request was submitted, with the highest date being the number of users currently licensed

I have the following query to select the licenses that have been updated, and it works if the license has only been resubmitted once. If it has been resubmitted more than once, it returns references to the previous update, and all updates before that.

SELECT p.id as id, c.id as oldid, p.numusers-c.numusers AS dif, p.date, c.date
FROM `licenses` AS p 
JOIN `licenses` AS c ON p.serial = c.serial 
                    AND p.date > c.date 
                    AND p.id <> c.id
WHERE p.id = 156
#GROUP BY p.id

Here is the dataset:

id  serial  numusers    date
26  1234    500         2010-07-14
34  1234    600         2010-07-15
156 1234    500         2010-07-21

When I run the query, I get the following:

id      oldid       dif         date          date
156     26          0          2010-07-21    2010-07-14
156     34          -100       2010-07-21    2010-07-15

If I uncomment the GROUP BY clause in the query, I get the row with the oldid of 26. How would I only select the row with the most recent date (row with oldid of 34)? I could use ORDER BY and LIMIT 1, but I also want to be able to select from the whole table without the WHERE clause.

I am using MySQL 5.1.

Answer 1

Would a derived table be any use; something like...

SELECT *
    FROM (SELECT p.id as id,
                 c.id as oldid,
                 p.numusers-c.numusers AS dif,
                 p.date,
                 c.date
              FROM `licenses` AS p JOIN `licenses` AS c ON p.serial = c.serial 
                   AND p.date > c.date 
                   AND p.id <> c.id
              ORDER BY oldid DESC) AS inner_table
    GROUP BY id;

Answer 2

Maybe what you want is:

select p.id, c.id as priorid, p.numusers-c.numusers AS dif, p.date, c.date as priordate    
from licenses p
join licenses c on c.serial=p.serial
  and c.date=(select max(date) from licenses ref where ref.serial=p.serial
    and ref.date<p.date)
order by p.serial

I've always found it a rather annoying limitation of SQL that to say "get me field X from the record with the max value for field Y" requires me to read the table once to find the max value of Y with an embedded query, and then read it again to re-find the record with that value and retrieve the other values that I want.

If there are three records for the same Serial, the above should give two "difference" lines on the output. I think that's what you're saying you want. If there is only one record for a given Serial, the above will give no output for that Serial, which may be what you want and maybe not.