How to order, group, order with mySQL

Question

Here is a simplified version of my table

tbl_records
-title
-created
-views

I am wondering how I can make a query where they are grouped by title, but the record that is returned for each group is the most recently created. I then will order it by views.

One way I guess is to do a sub query and order it by created and then group it by title and then from those results order it by views. I guess there is a better way though.

Thanks

EDIT:

SAMPLE DATA:

-title: Gnu Design
-created: 2009-11-11 14:47:18
-views: 104

-title: Gnu Design
-created:2010-01-01 21:37:09
-views:9

-title: French Connection
-created:2010-05-01 09:27:19
-views:20

I would like the results to be:

-title: French Connection
-created:2010-05-01 09:27:19
-views:20

-title: Gnu Design
-created:2010-01-01 21:37:09
-views:9

Only the most recent Gnu Design is shown and then the results are ordered by views.

Answer 1

 select i.*, o.views from
   (
       select
       title
       , max(created) as last_created
       from tbl_records
       group by title
   ) i inner join tbl_records o
   on i.title = o.title and i.last_created = o.created
   order by o.views desc

I'm assuming that the aggregation to be applied to views is count(), but could well be wrong (you'll need to have some way of defining which measure of views you wish to have for the lastest created title). Hope that helps.

EDIT: have seen your sample data and edited accordingly.

Answer 2

SELECT title, 
       MAX(created), 
       views 
FROM table 
GROUP BY title 
ORDER BY views DESC

Answer 3

This is an example of the greatest-n-per-group problem that appears frequently on StackOverflow.

Here's my usual solution:

SELECT t1.*
FROM tbl_records t1
LEFT OUTER JOIN tbl_records t2 ON (t1.title = t2.title AND 
  (t1.created < t2.created OR t1.created = t2.created AND t1.primarykey < t2.primarykey))
WHERE t2.title IS NULL;

Explanation: find the row t1 for which no other row t2 exists with the same title and a greater created date. In case of ties, use some unique key to resolve the tie, unless it's okay to get multiple rows per title.