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;