PostgreSQL trying to get multiple unique rows

Question

I'm trying to get multiple unique rows in a query, the table I'm working with it is:

settings

id	category_id	key	value
1	null	phone	+2222222222
2	1	phone	+1111111111
3	null	email	[email protected]

So there is a default settings with a null value, and each category can have their own settings, the query that I'm running is:

SELECT MAX(category_id), key, value
FROM settings
WHERE key IN('phone', 'email')
AND (category_id IS NULL OR category_id = 1)
GROUPBY key, value;

But instead return me one value per key, returns both rows.

I want the query return me something like this:

id	category_id	key	value
2	1	phone	+1111111111
3	null	email	[email protected]

if the category_id exists just return that row, not the row with category_id null

You are grouping by value. Since all three rows have different values in it, the query will produce three rows. Please add the expected result to the question. — The Impaler
– The Impaler, Commented Oct 21, 2022 at 14:03
Ah... seems like the row with 1 has more value than the row with null. Is that correct? — The Impaler
– The Impaler, Commented Oct 21, 2022 at 14:10

nbk · Accepted Answer · 2022-10-21 14:11:29Z

1

For that you can only GROUP BY key, so you need to add a aggregation function for value, depending on what you want

SELECT MAX(category_id), key, MIN(value)
FROM settings
WHERE key IN('phone', 'email')
AND (category_id IS NULL OR category_id = 1)
GROUP BY key;

max	key	min
null	email	[email protected]
1	phone	+1111111111

SELECT 2

answered Oct 21, 2022 at 14:11

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1