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.
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With