How to get mutiple results from group by in postgres
I have a table with some data that looks like:
id | type | timestamp | 1 | 开发者_JAVA技巧 1 | 0001 | 2 | 2 | 0002 | 3 | 3 | 0003 | 4 | 2 | 0004 | 5 | 2 | 0005 | 6 | 2 | 0006 | 7 | 2 | 0007 | 8 | 2 | 0008 | 9 | 2 | 0009 | 10 | 3 | 0010 |
What I need is the 5 most recent for each type, order doesn't really matter.
id | type | timestamp | 1 | 1 | 0001 | 5 | 2 | 0005 | 6 | 2 | 0006 | 7 | 2 | 0007 | 8 | 2 | 0008 | 9 | 2 | 0009 | 3 | 3 | 0003 | 10 | 3 | 0010 |
I'm guessing a sub-select, group by or some sort of union is in order but I'm just not sure how to go about getting it.
You can use Window function. See manual.
Example:
SELECT id, type, timestamp
FROM
(SELECT id, type, timestamp,
rank() OVER (PARTITION BY type ORDER BY timestamp DESC) AS pos
FROM table
) AS t
WHERE pos < 5;
精彩评论