开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜