Уникальная агрегатная функция, когда единственное значение гарантируется предложением WHERE

Question

Учитывая следующее:

CREATE TABLE A (A1 INTEGER, A2 INTEGER, A3 INTEGER);

INSERT INTO A(A1, A2, A3) VALUES (1, 1, 1);
INSERT INTO A(A1, A2, A3) VALUES (2, 1, 1);

Я хочу выбрать максимум A1 учитывая конкретные A2 а также A3 значения, и имеют эти значения (A2 а также A3) также появляются в возвращенной строке (например, чтобы я мог использовать их в соединении, так как SELECT ниже предназначен для подзапроса).

Казалось бы логичным сделать следующее, учитывая, что A2 и A3 жестко закодированы в WHERE пункт:

SELECT MAX(A1) AS A1, A2, A3 FROM A WHERE A2=1 AND A3=1

Тем не менее, PostgreSQL (и я подозреваю, что и другие RDBM также) отклоняется и запрашивает агрегатную функцию для A2 и A3, даже если их значение фиксировано. Поэтому вместо этого я должен сделать:

SELECT MAX(A1) AS A1, MAX(A2), MAX(A3) FROM A WHERE A2=1 AND A3=1

или:

SELECT MAX(A1) AS A1, 1, 1 FROM A WHERE A2=1 AND A3=1

Первая альтернатива мне не нравится, потому что я мог бы использовать MIN вместо этого он все равно будет работать, тогда как второй вариант удваивает количество позиционных параметров, чтобы обеспечить значения при использовании из интерфейса языка программирования. В идеале я бы хотел UNIQUE агрегатная функция, которая утверждает, что все значения равны и возвращает это единственное значение, или даже RANDOM агрегатная функция, которая будет возвращать одно значение случайным образом (так как я знаю из WHERE оговорка, что они все равны).

Есть ли идиоматический способ написать выше в PostgreSQL?

Answer 1

Еще проще, вам нужно только ORDER BY / LIMIT 1

SELECT a1, a2, a3      -- add more columns as you please
FROM   a
WHERE  a2 = 1 AND a3 = 1
ORDER  BY 1 DESC      -- 1 is just a positional reference (syntax shorthand)
LIMIT  1;

LIMIT 1 это специфический синтаксис Postgres.
Стандарт SQL будет:

...
FETCH FIRST 1 ROWS ONLY

Мой первый ответ с DISTINCT ON был для более сложного случая, когда вы хотите получить максимум a1 по различным комбинациям (a2,a3)

В сторону: я использую строчные идентификаторы по причине.

Answer 2

Как насчет группы по

select 
a2
,a3
,MAX(a1) as maximumVal
from a
group by a2, a3

Answer 3

Это работает для вас?

select max(A1),A2,A3 from A GROUP BY A2,A3;

РЕДАКТИРОВАТЬ

select A1,A2,A3 from A where A1=(select max(A1) from A ) limit 1

Answer 4

Стандартный прием для получения максимальной строки без агрегатной функции состоит в том, чтобы гарантировать отсутствие большего значения с помощью подзапроса NOT EXISTS. (Это не работает, когда есть связи, но не будет и подзапрос с максимальным значением). При необходимости не составит труда добавить условие прерывателя связей.

Другим решением будет подзапрос с оконной функцией row_number() или же rank()

SELECT *
FROM a src
WHERE NOT EXISTS ( SELECT * FROM a nx
    WHERE nx.a2 = src.a2
    AND nx.a3 = src.a3
    AND nx.a1 > src.a1
    );