Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

DISTINCT ON (first_column) + ORDER BY second_column

Posted Август 12th, 2009 by Diem
in 

CREATE TABLE sample (
  first_column int,
  second_column int
);

Нужно сделать выборку с условием DISTINCT ON ( first_column )
Но при этом postgresql требует в ORDER BY первым полем ставить first_column
А мне нужна сортировка совсем по другому полю ( second_column ).

Можно ли такое реализовать средствами sql в postgresql не прибегая к вложенным запросам ?

‹ Проблема в реализации array -> table как? ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Попробуйте заменить DISTINCT

On Август 12th, 2009 admin says:

Попробуйте заменить DISTINCT на GROUP BY. Мне трудно дать конкретный совет, потому что я не вижу подробностей в плане, что есть и что вы хотите получить после запроса.

Да GROUP BY тоже

On Август 12th, 2009 Diem says:

Да GROUP BY тоже пробовал
скажем при вот таком запросе:

SELECT first_column, second_column FROM sample
GROUP BY first_column
ORDER BY second_column

я получаю ERROR: колонка "sample.second_column" должна фигурировать в выражении GROUP BY или использоваться в агрегатной функции

Если делать GROUP BY first_column, second_column я уже не получу желаемый результат, будут выбраны first_column, с одинаковыми значениями

Пока что решением у меня является что то на подобие:

SELECT t.first_column, t.second_column FROM (
  SELECT DISTINCT ON (first_column)
    first_column, second_column FROM sample
  ORDER BY first_column
) AS t
ORDER BY t.second_column;

Ну дайте же данные какие-либо

On Август 12th, 2009 admin says:

Ну дайте же данные какие-либо поиграться :)
Может чего наковыряю :)

Это как пример, но мою

On Август 12th, 2009 Diem says:

Это как пример, но мою ситуацию повторяет.

Предположим что есть некая online игра, в которую зарегестрированным пользователям (таблица players) можно поиграть, а результат (набранное колличество очков) фиксируется в таблице stat

CREATE TABLE players (
    id serial PRIMARY KEY,
    login varchar(15)
);

INSERT INTO players (login) VALUES ('Vasya'), ('Kolya'), ('Petya'), ('Igor'), ('Roma');

CREATE TABLE stat (
    player_id int NOT NULL REFERENCES players(id),
    points int NOT NULL,
    stamp timestamp NOT NULL
);

INSERT INTO stat (player_id, points, stamp) VALUES
(1, 100, '2009-08-01 19:03:50'),
(1, 200, '2009-08-02 19:03:40'),
(2, 99 , '2009-07-01 19:03:20'),
(2, 199, '2009-09-04 19:03:30'),
(2, 300, '2009-08-03 19:23:00'),
(3, 154, '2009-08-11 19:44:30'),
(3, 400, '2009-08-01 19:03:21'),
(4, 80,  '2009-08-05 19:03:00'),
(4, 110, '2009-08-04 19:23:30'),
(5, 210, '2009-08-01 19:53:00');

Я выбираю все данные, отсортированные по полю points:

SELECT players.login, stat.points, stat.stamp
FROM stat JOIN players ON (players.id = stat.player_id)
ORDER BY points DESC;

 login | points |        stamp        
-------+--------+---------------------
 Petya |    400 | 2009-08-01 19:03:21
 Kolya |    300 | 2009-08-03 19:23:00
 Roma  |    210 | 2009-08-01 19:53:00
 Vasya |    200 | 2009-08-02 19:03:40
 Kolya |    199 | 2009-09-04 19:03:30
 Petya |    154 | 2009-08-11 19:44:30
 Igor  |    110 | 2009-08-04 19:23:30
 Vasya |    100 | 2009-08-01 19:03:50
 Kolya |     99 | 2009-07-01 19:03:20
 Igor  |     80 | 2009-08-05 19:03:00

Но мне нужно, чтоб если запись уже выбрана,
то остальные записи с таким же логином (+ в данной ситуации уже с меньшим колличеством очков) - игнорировались
поле stamp сдесь не играет никакой роли

Логично наверное делать так:

SELECT DISTINCT ON (stat.player_id) players.login, stat.points, stat.stamp
FROM stat JOIN players ON (players.id = stat.player_id)
ORDER BY points DESC;

ERROR: Выражения SELECT DISTINCT ON должны совпадать с изначальными выражениями ORDER BY

Или так:

SELECT players.login, stat.points, stat.stamp
FROM stat JOIN players ON (players.id = stat.player_id)
GROUP BY stat.player_id
ORDER BY points DESC;

ERROR: колонка "players.login" должна фигурировать в выражении GROUP BY или использоваться в агрегатной функции

P.S. я ошибся, в предыдущем посте решение с вложенным запросом не даёт желаемого результата.

> поле stamp сдесь не играет

On Август 12th, 2009 admin says:

> поле stamp сдесь не играет никакой роли
Так его надо выводить или нет?
Если нет, то проще некуда:

SELECT players.login, max(stat.points) AS points FROM stat JOIN players ON (players.id = stat.player_id)
GROUP BY login ORDER BY points DESC;

 login | points 
-------+--------
 Petya |    400
 Kolya |    300
 Roma  |    210
 Vasya |    200
 Igor  |    110
(5 rows)

Наполовину решено

On Август 12th, 2009 Diem says:

SELECT players.login, MAX(stat.points) AS max_points
FROM stat JOIN players ON (players.id = stat.player_id)
GROUP BY players.login
ORDER BY max_points DESC;

 login | max_points 
-------+------------
 Petya |        400
 Kolya |        300
 Roma  |        210
 Vasya |        200
 Igor  |        110

Вот собственно и есть нужный результат.
Но как быть с полем stamp?

Как мне указать, что нужно выбрать именно тот stamp, который присутствует в записи
с выбранным points, существует ли такая агрегаторная функция или другой метод ?

Sorry за кривую терминологию, но надеюсь задача ясна :)

Да прям real time общение

On Август 12th, 2009 Diem says:

Прям real time общение такое :)
Да нужно вывести stamp, но на последовательность резултатов он не влияет.

Что-то совсем мозги заплыли

On Август 12th, 2009 admin says:

Что-то совсем мозги заплыли жиром. Подзапросом сделал, но как-то криво по-моему:

SELECT P.login, P.points, stat.stamp FROM
(SELECT players.login, MAX(stat.points) AS points  FROM 
stat JOIN players ON (players.id = stat.player_id)
GROUP BY login) AS P 
JOIN players ON players.login=P.login
JOIN stat ON (stat.points=P.points AND stat.player_id=players.id)
ORDER BY points DESC;

Работает, конечно, но меня не покидает сомнение, что всё можно сделать как-то изящней.

Большое спасибо за помощь

On Август 12th, 2009 Diem says:

Если я не ошибаюсь в mysql GROUP BY работает совсем по другому, и если я снова таки не ошибаюсь MAX (stat.points) не дал бы результатов, так как выбиралось бы максимум только из одного значения, соответствующего выбранному полю. Зато работает конструкция GROUP BY player_id ORDER BY points.
И я считаю что это только + для postgres, так как можно решить очень много задач используя GROUP BY.
(под рукой mysql нет, по этому могу ошибатся)

Да вот и у меня в голову ничего не пришло, кроме как заджоинить stat.
Большое спасибо за помощь, ситуация всё таки более менее распространённая, наверняка здесь есть простое решение.
Пока использую ваш вариант.

Про DISTINCT ON вообще в

On Август 12th, 2009 admin says:

Про DISTINCT ON вообще в мануале написано:
Предложение DISTINCT ON не является частью стандарта SQL и иногда считается плохим стилем, потому что потенциально может привести к неожиданным результатам. При разумном использовании GROUP BY и подзапросов в FROM, данное предложение может быть опущено, но часто оно является наиболее удобной альтернативой.

А вот ещё как можно было бы

On Август 12th, 2009 admin says:

А вот ещё как можно было бы ситуацию упростить:

CREATE VIEW tmp_stat AS
SELECT players.login, players.id, MAX(stat.points) AS points
FROM stat JOIN players ON players.id = stat.player_id GROUP BY login, id;

и потом уже
SELECT T.login, T.points, S.stamp 
FROM tmp_stat T JOIN stat S ON (T.id=S.player_id AND T.points=S.points)
ORDER BY T.points DESC;

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023