select
keys.*
from keys.*
join
(select max(issued_at) as issued_at, user_login from keys group by user_login) as last
on
last.user_login = keys.user_login and last.issued_at = keys.issued_atselect
"user_login",
(select key "user_login" from keys as last where keys.id = last.id order by issued_at desc limit 1) as key
from keys
group by "user_login"SELECT
user_login,
LAST_VALUE(key) OVER(ORDER BY issued_at desc) as last_key
FROM
keys
group by "user_login";SELECT
user_login,
ARRAY_AGG(key ORDER BY issued_at desc)[0]
FROM
keys
group by "user_login";WITH
some_data AS ( select val from unnest(ARRAY['AAAABB', 'AAABBB']) as val)
SELECT *
FROM some_data;select
"product_id" AS "id",
min("price_fallback") AS "price",
(array_agg("price_base" ORDER BY price_fallback DESC))[1] as "price_old"
from "cache"
WHERE ("showcase_id" IN (66, 1))
group by "product_id"
order by "product_id"
limit 50;update "notifications"
set "is_read" = 1,
"updated_at" = 2023 - 02 - 01 12:26:05
from ( select id from "notifications" where “client_id” = 126473 and "notifications"."client_id" is not null order by id for update) as t
where "notifications"."id" = t.id ;create table public.profiles (
id uuid not null,
username text default concat('Пользователь #', random()*100000),
primary key (id)
);with t as(SELECT table_name, column_name, data_type, ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY column_name) as num
FROM information_schema.columns
WHERE table_schema = 'public')
select json_agg(json_build_object(num, json_build_object(column_name, data_type))) from t group by table_name;SELECT o.*, s.expected_at FROM order AS o
LEFT JOIN (select min(expected_at) as expected_at, order_id from supply group by order_id) AS s ON o.id = s.order_id
ORDER BY expected_at ASCSELECT b.id, b.title,
CASE WHEN exists (SELECT ib.book_id FROM issued_book ib where ib.book_id = b.id AND ib.date_return IS NULL)
THEN 'Нет в наличии'
ELSE 'В наличии'
END AS Наличие
FROM book b;select string_agg(id::text, ','), tag from table group by tag having count(*)>1;SELECT id, created_at, contact_id, status
FROM contact_status
WHERE
contact_status.id in (select max(id) as id from contact_status group by contact_id)
contact_status.status = 'authorized'
ORDER BY contact_id, created_at DESCSELECT id, created_at, contact_id, status
FROM contact_status
WHERE
contact_status.id in (select max(id) as id from contact_status where contact_id=121750 group by contact_id )
contact_status.status = 'authorized'
ORDER BY contact_id, created_at DESC