1

I have this table :

+----+--------+--------+-----------+----------------------------+
| id |  type  | amount | dealer_id |            date            |
+----+--------+--------+-----------+----------------------------+
| 12 | sub_s  | 29.00  |       502 | 2020-05-21 10:05:41.725+02 |
| 11 | sub_pe | 79.00  |       501 | 2020-05-20 09:05:41.725+02 |
| 10 | sub_pe | 79.00  |       501 | 2020-05-19 09:05:41.725+02 |
|  9 | sub_pe | 79.00  |       501 | 2020-05-18 09:05:41.725+02 |
|  8 | sub_pe | 79.00  |       501 | 2020-05-17 09:05:41.725+02 |
|  7 | sub_pe | 79.00  |       501 | 2020-05-16 09:05:41.725+02 |
|  6 | sub_pe | 79.00  |       501 | 2020-05-15 09:05:41.725+02 |
|  5 | sub_pe | 79.00  |       501 | 2020-05-14 09:05:41.725+02 |
|  4 | sub_pe | 79.00  |       501 | 2020-05-13 09:05:41.725+02 |
|  1 | sub_pe | 79.00  |       501 | 2020-05-12 09:05:41.725+02 |
+----+--------+--------+-----------+----------------------------+

How can I SELECT only the first 6 rows (order by date ASC) for each dealer_id ?

So to have as result :

+----+--------+--------+-----------+----------------------------+
| id |  type  | amount | dealer_id |            date            |
+----+--------+--------+-----------+----------------------------+
| 12 | sub_s  | 29.00  |       502 | 2020-05-21 10:05:41.725+02 |
|  8 | sub_pe | 79.00  |       501 | 2020-05-17 09:05:41.725+02 |
|  7 | sub_pe | 79.00  |       501 | 2020-05-16 09:05:41.725+02 |
|  6 | sub_pe | 79.00  |       501 | 2020-05-15 09:05:41.725+02 |
|  5 | sub_pe | 79.00  |       501 | 2020-05-14 09:05:41.725+02 |
|  4 | sub_pe | 79.00  |       501 | 2020-05-13 09:05:41.725+02 |
|  1 | sub_pe | 79.00  |       501 | 2020-05-12 09:05:41.725+02 |
+----+--------+--------+-----------+----------------------------+
IndiaSke
  • 348
  • 1
  • 2
  • 10

1 Answers1

2

With ROW_NUMBER() window function:

select t.id, t.type, t.amount, t.dealer_id, t.date
from (
  select * ,
    row_number() over (partition by dealer_id order by date) rn
  from tablename
) t
where t.rn <= 6
forpas
  • 160,666
  • 10
  • 38
  • 76