CODEWITHSUNDEEP
sqloracle-database
Pablo Bongiolatti
Pablo Bongiolatti

Reputation: 1

Select distinct on result

I have a stored procedure and it has this case:

CASE
WHEN ED.DESCRIPCION IS NOT NULL THEN ED.DESCRIPCION
WHEN  adjuntos > 0 THEN 'PRES' 
WHEN adjuntos=0 AND obligatory = 'N' THEN 'NO'
ELSE 'PEN'
END AS STATE

I need to select distinct values of that result STATE, something like placing the DISTINCT before the case, this stored procedure currently returns:

PRES
NO
PEN
PEN
PEN
PEN

Upvotes: 0

Views: 510

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You literally just need to add the DISTINCT keyword:

SELECT DISTINCT CASE
WHEN ED.DESCRIPCION IS NOT NULL THEN ED.DESCRIPCION
WHEN  adjuntos > 0 THEN 'PRES' 
WHEN adjuntos=0 AND obligatory = 'N' THEN 'NO'
ELSE 'PEN'
END AS STATE
FROM ...

db<>fiddle with some made-up data.

If you are selecting other columns at the same time then you need DISTINCT before the first column expression in the select list. It applies to the entire row of the result set, not to individual columns; and it isn't a function. From the documentation:

Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.

But you have only shown one column expression, so this doesn't seem to apply.

Upvotes: 1

Related Questions