Goal: Selecting the top value for by group (such as highest date or largest value)

Difficulty: Easy

Prerequisites: Access to run SELECT queries on MySQL

Here is the data in our example table:

Table: Images

ID          Gallery          Submitted                  Description
-------     --------         -----------------          ------------------------
1           Cats             12-01-2012 00:00:12        This is my pretty kitty
2           Dogs             12-01-2012 03:00:12        Bowowow
3           Dogs             12-04-2012 05:00:12        Who let the dogs out
4           Cats             12-05-2012 01:00:12        MEOOOW
5           Birds            12-06-2012 01:00:12        Polly Wants a Cracker

Now in our example we want to find the last image submitted for each gallery.  Now normal Group By queries can get you the Gallery and Submitted field, unfortunately you cannot also retrieve the ID and description with these when using a group by. So in my query example below I join the table back to itself and find the largest submitted time in a group by using a left join, which will allow us to identify the largest records by finding the rows where the left join failed to join to anything that was bigger than itself.

SELECT i1.*
FROM Images AS i1 LEFT JOIN images AS i2
ON (i1.Gallery = i2.Gallery AND i1.Submitted < i2.Submitted)
WHERE i2.Submitted IS NULL;

Running the query above will get you the following results table:

ID          Gallery          Submitted                  Description
-------     --------        -----------------         ------------------------
3           Dogs             12-04-2012 05:00:12        Who let the dogs out
4           Cats             12-05-2012 01:00:12        MEOOOW
5           Birds            12-06-2012 01:00:12        Polly Wants a Cracker

And there you have it.  A simple and efficient way to get the largest record in a group. 

Search