Search code examples
sqlpostgresqlgreatest-n-per-group

How to find maximum and value of column with the same column in postgres?

I want to find maximum value of the has_sold column in an specific province.For example in all rows with province = Tehran which row has the most has_sold value? This is my table:

CREATE TABLE fp_stores_data_test (
    time INTEGER,
    province VARCHAR(20),
    city VARCHAR(20),
    market_id INTEGER,
    product_id INTEGER,
    price INTEGER, 
    quantity INTEGER,
    has_sold INTEGER,
    PRIMARY KEY (time, city, province, market_id ,product_id)
);

I had tried this SELECT DISTINCT city, max(has_sold) FROM fp_stores_data_test GROUP BY city, but I'm not sure it's right.

Solution

  • You can try the below one using row_number()

    select * from
(
select *, row_number() over(partition by province order by has_sold desc) as rn
from fp_stores_data_test
)A where rn=1