How to order, group, order using mySQL

Question

Here is a simplified version of my table

tbl_records -title -created -views 

I am wondering how I can make a request where they are grouped by name, but the record that is returned for each group is the most recently created. Then I will order it point by point.

One of the ways, I think, is to make an additional request and arrange it created, and then group by name, and then order it from the results from the submissions. I think there is a better way.

thanks

EDIT:

SAMPLES 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 latest Gnu design is shown, and then the results are sorted by submission.

Answer 1

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

Here is 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 string t1 for which there is no other string t2 with the same title and a larger created date. In the case of links, use a unique key to resolve the link, unless you can get a few lines for the title .

Answer 2

  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 assume that the aggregation that will be applied to views is count() , but it may well be erroneous (you will need to determine how to measure which dimensions you want to use for the last title). Hope this helps.

EDIT : reviewed your sample data and edited accordingly.

Answer 3

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