PostgreSQL从行中选择最大值 [英] PostgreSQL select max from rows

问题描述

我有以下数据:

CREATE TABLE offer (
        id INTEGER,
        product_id VARCHAR,
        created_at TIMESTAMP,
        amount INTEGER,
        PRIMARY KEY (id));

INSERT INTO offer (id, product_id, created_at, amount)
VALUES
        (1, '123', '2016-03-12', 990),
        (2, '136', '2016-02-01', 1056),
        (3, '111', '2016-01-01', 1000),
        (4, '123', '2016-01-02', 500);

我想获得每个product_id数量最多的行. 如果我使用这些先前的行，我想获取ID:2、3和1，因为第1行包含的行数比第4行要大.

And I would like to get rows with the highest amount per product_id. If I take these previous rows I would like to get IDs: 2, 3 and 1 because row 1 contains a greater amount than row 4.

 id | product_id |     created_at      | amount
----+------------+---------------------+--------
  2 | 136        | 2016-02-01 00:00:00 |   1056
  3 | 111        | 2016-01-01 00:00:00 |   1000
  1 | 123        | 2016-03-12 00:00:00 |    990

我尝试过类似的方法，但是我不确定:

I tried something like that but I'm not sure about it:

SELECT id, product_id, created_at, amount 
FROM offer
ORDER BY 4, 2 DESC, 1, 3

我还无法尝试.

推荐答案

如果我理解正确，则可以使用distinct on:

If I understand correctly, you can use distinct on:

select distinct on (product_id) o.*
from offers o
order by product_id, amount desc;

distinct on是Postgres扩展名.在这种情况下，每个product_id返回一行.特定行是数量最多的行，由amount desc确定.

distinct on is a Postgres extension. In this case, it returns one row per product_id. The particular row is the one with the largest amount, as determined by amount desc.

这篇关于PostgreSQL从行中选择最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！