sub-select to find max values with a group

Several times recently I've needed to get the max or the min value within a group of values. For example, say I want the max id for a user each day or the max id for each user who has several entries in some kind of a history table.

 At first I struggled with how I was going to get that data but the solution was actually fairly simple with a sub-select and GROUP BY. Since I realized how easy this is to do, I've had to do a very similar thing at work quite often. It is nice to remember how useful GROUP BY can be. This is a similar query I wrote recently with a GROUP BY and a sub-select. 

This trick only works when the table has an auto-increment id. You also have to make an assumption for your data that the max auto increment id has the highest value you are looking for in the group. 

Might result in full index scans so it may not be the most optimal solution but it worked for what I needed it for:

    

    SET @day_number = 1;

    SELECT id

    FROM table_a

    WHERE date_created BETWEEN DATE_SUB(NOW(), INTERVAL @day_number DAY) AND NOW()

    AND value1 < (@day_number*86400)

    AND id IN (SELECT MAX(id) FROM table_a GROUP BY user_id);