How to Limit Duplicat Rows in Postgres sql

user_7946105 0
Viewed 54

Hi I have data similar to for table : fruit_table

Product Code Product Date
MG Mango 2020-01-25
MG Mango 2020-01-26
MG Mango 2020-01-27
MG Mango 2020-01-28
BN Banana 2019-01-15
BN Banana 2020-01-19
BN Banana 2020-01-20
BN Banana 2016-01-20
AP APPLE 2021-03-02

As you can see in the data we have Mango 4 products and Banana 4 Products and Apple with 1 product, i want the solution is to limit the products with 2 rows with latest date.

And i want output similar to

Product Code Product Date
MG Mango 2020-01-27
MG Mango 2020-01-28
BN Banana 2020-01-19
BN Banana 2020-01-20
AP APPLE 2021-03-02

How can this be achieved with a simple query in PostgreSQL query. Thanks in advance.

postgresql sql
2 Answers

demo:db<>fiddle

You can use row_number() window function to achieve that:

SELECT
    *
FROM (
    SELECT
        *,
        row_number() OVER (PARTITION BY "Product Code" ORDER BY "Date" DESC)     -- 1
   FROM fruit_table
) s
WHERE row_number <= 2                                                            -- 2
  1. row_number() adds a row count to each record in an ordered group (= partition). In your case the group is the Product Code (or Product) and you need to order each group by Date DESC to get the most recent dates to the top. Now the most recent date in each group gets the row count 1, the second recent one the 2 and so on
  2. Using this row count you can filter only the two top records of each group.
user_39842210

As a note: If you have a separate table of products, then you might find that a lateral join is faster:

select ft.*
from products cross join lateral
     (select ft.*
      from fruit_table ft
      where ft.product = p.product
      order by ft.date desc
      limit 2
     ) ft;

Like the solution using row_number() this will take advantage of an index on fruit_table(product, date). However, I think the performance would usually be a little better (basically because it does not assign row number values to all rows before filtering them out).

user_11440350
Related