Re: group by / order by problem (how to sort before group)

[Peter Brawley]

Given a table titles( id int, pubid int, avail date ), retrieving the ten most recent title ids, one per publisher, is an instance of the within-group aggregates query pattern described at http://www.artfulsoftware.com/mysql-queries.php, click on 'Aggregates' in the left panel, then on 'Within-group aggregates' ...

SELECT t1.*
FROM titles t1
LEFT JOIN titles t2 ON t1.pubid=t2.pubid AND t1.avail<t2.avail
WHERE t2.pubid IS NULL
LIMIT 10;

It's hard to imagine a good reason for implementing this as a physical table that needs to be updated. It's a report query, so just save it as a View.

PB