Skip to content
Advertisement

Using COALESCE correctly in WHERE clause

Can someone explain why coalesce doesn’t work in the where clause given the following conditions below? How do we use coalesce correctly in this case without changing the below coalesce conditions and only for spoiled = Y?

Table Fruit:

Query:

Using the query above will not return anything. Desired output should be grapes.

Desired Output:

Advertisement

Answer

We can use ROW_NUMBER here to select what you want with priorities:

The idea here is to assign a priority from 1 to 5 for each item which is spoiled. We use ROW_NUMBER to generate a sequence always starting with 1 being the highest available priority.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement