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

settings

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:

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

CodePudding user response：

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;

SELECT 2

fiddle