Returning latest/highest record from a set of records

Sun, 2011-04-10 14:09 -- admin

A common reporting requirement in database applications is selecting the latest purchase, or latest job, or oldest chlld for each person in a table.  Any beginning student of SQL knows how to return the most recent date in a table:

SELECT MAX(rec_date) FROM sales;

 2011-03-15 18:04:45.178057-04
(1 row)                                                           

But that doesn't tell us which person owns that record.  We can do this using a sub, or nested, query on the same table:

SELECT cust_name FROM sales WHERE rec_date = (SELECT MAX(rec_date) FROM sales);

  cust_name |          rec_date
 Dwight Schrute       | 2011-03-15 18:04:45.178057-04
(1 row)                                                 

This could return multiple records with the same rec_date.  But let's say we're not interested in who owns the most recent record.  Instead, we want to know the latest record for everyone in the table, such as the last sales date for every customer. Here's one of the possible solutions for that:

Subscribe to RSS - php