SQL: Как вытащить уникальные записи по последней дате?

Question

[Станислав]

Привет! Как вытащить записи с уникальными именмиа файлов последней версии или последней даты создания для определенного юзера (т.е. filename не должен повторятся, а если повторяется выводить последнюю версию либо дату создания (не важно) ).
Есть таблица

| id  | filename | user_id | version | created_at |
| --- | -------- | ------- | ------- | ---------- |
| 1   | one      | 1       | 1       | 2022-01-01 |
| 2   | one      | 1       | 2       | 2022-01-02 |
| 3   | some     | 1       | 1       | 2022-01-03 |
| 4   | some     | 2       | 1       | 2022-01-03 |

В результате должно быть

| id  | filename | user_id | version | created_at |
| --- | -------- | ------- | ------- | ---------- |
| 2   |   one    |    1    |    2    | 2022-01-02 |
| 3   | some     | 1       | 1       | 2022-01-03 |

Попытался сделать через DISTINCT ON, но так получаю ошибку из-за сортировки.

SELECT DISTINCT ON (filename, user_id) *
			  FROM block
			  WHERE user_id = 1
			  ORDER BY created_at

Comments

[ThunderCat]

Во первых, не указана бд, во вторых - ордер должен быть деск.

[Akina]

И не только СУБД, но и её точная версия - это может сильно повлиять на то, как будет выглядеть оптимальное решение.

[Станислав]

ThunderCat, Akina извините тупанул) База PostgreSQL 12

[Akina]

База PostgreSQL 12

Тогда без вариантов - ROW_NUMBER() в CTE. Всё остальное - или туфта, или лишний геморрой.

Answer 1

[Slava Rozhnev]

В соответствии с наставлениями Akina :

with order_files as (
	select 
  		*, 
  		row_number() over (partition by filename order by version desc, created_at desc) rn
  	from files 
  	where user_id = 1
) select id, filename, user_id, version, created_at
from order_files where rn = 1
;

online sql editor

Comments

[Станислав]

Спасибо большое!

Answer 2

[Владимир Юрченков]

SELECT * FROM block AS a
WHERE created_at = (SELECT MAX(created_at) FROM block AS b WHERE a.user_id = b.user_id) ORDER BY a.user_id

Я может мог написать фигню, но подставьте свое. И да, такое лучше с мелкими таблицами делать.

Comments

[Станислав]

Спасибо, но к сожалению не верно. Т.к. в этом случае выбирается записи по последней дате и если у юзера нет записей в эту дату ничего не будет.
А нужно вывести все имена файлов для юзера без повторений. А если имя повторяется выбрать последнюю версию или дату .