mysql order by timestamp latest and group the rows of that category with limit
Order 开发者_如何学编程by timestamp DESC and group by a category with a limit in Mysql
cat timestamp
----------------
11 1308748405
11 1308747228
13 1308749273
57 1308748289
11 1308746228
...to get a table of the format, where it is sorted by timestamp and also relevant rows of that category grouped together with a limit. (ie group by category of all rows and arrange amoung the groups by the descending order of timestamps but keep the group together)
cat timestamp
----------------
13 1308749273
11 1308748405
11 1308747228
57 1308748389
I am guessing what you are looking for is: Get the minimum timestamp per category, and sort the results by timestamp?
SELECT t.cat, t.timestamp
FROM table t
JOIN (SELECT MIN(timestamp) as min_time, cat
FROM table
GROUP BY cat) as tmp
ON (t.cat = tmp.cat AND t.timestamp = tmp.min_time)
ORDER BY 2 DESC
SELECT cat, timestamp
FROM yourtable
ORDER BY timestamp DESC
GROUP BY cat
LIMIT 4
without more details, this is the best you're going to get.
If you want to get 4 groups based on sorted by it's latest timestamp. This may works:
SELECT tblTes.cat, tblTes.timestamp
FROM tblTes INNER JOIN (SELECT cat, MAX(TIMESTAMP) AS latest FROM tblTes GROUP BY cat) AS subQuery
ON tblTes.cat = subQuery.cat AND tblTes.timestamp = subQuery.latest
ORDER BY tblTes.timestamp DESC LIMIT 4;
精彩评论