How to get mutiple results from group by in postgres

1
votes

I have a table with some data that looks like:

id | type | timestamp |
 1 |    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.

1
sqlpostgresql

1 Answers

2
votes

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;