Skip to content

Instantly share code, notes, and snippets.

@Kostanos
Created July 20, 2022 12:02
Show Gist options
  • Save Kostanos/1a1e2b6498924bd6b550e6f8aa55b300 to your computer and use it in GitHub Desktop.
Save Kostanos/1a1e2b6498924bd6b550e6f8aa55b300 to your computer and use it in GitHub Desktop.
Select Group with Limit 1 Speed
-- CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
id UUID primary key DEFAULT uuid_generate_v4(),
title text
);
CREATE TABLE products (
id UUID primary key DEFAULT uuid_generate_v4(),
category_id UUID,
title text,
foreign key (category_id)
REFERENCES categories (id)
);
INSERT INTO categories (
title
) SELECT
md5(random()::text)
FROM generate_series(1, 100) s(i);
INSERT INTO products (
category_id, title
)
WITH expanded AS (
SELECT RANDOM(), seq, c.id AS category_id
FROM generate_series(1, 100000) seq, categories c
), shuffled AS (
SELECT e.*
FROM expanded e
INNER JOIN (
SELECT ei.seq, MIN(ei.random) FROM expanded ei GROUP BY ei.seq
) em ON (e.seq = em.seq AND e.random = em.min)
ORDER BY e.seq
)
SELECT
s.category_id,
md5(random()::text)
FROM shuffled s;
-- slitely adapted to PostgresQL
SELECT
id,
category_title,
(array_agg(product_title))[1]
FROM
(SELECT c.id, c.title AS category_title, p.id AS product_id, p.title AS product_title
FROM categories AS c
JOIN products AS p ON c.id = p.category_id
ORDER BY c.id ASC) AS a
GROUP BY id, category_title;
SELECT
c.id,
c.title,
p.id AS product_id,
p.title AS product_title
FROM categories AS c
JOIN products AS p ON
p.id = ( --- the PRIMARY KEY
SELECT p1.id FROM products AS p1
WHERE c.id=p1.category_id
ORDER BY p1.id LIMIT 1
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment