Site icon Valentino Outlet Online

POSTGRES: How to select rows with a certain value only if another value doesn’t exist, and in that case select the other value?

Here is an example of the table I am working withWhat I would like to achieve is to select the most recent row where the type is not ‘NONE’ unless ‘NONE’ is the only type available for that id.

id date type
123 01-01-2021 NONE
123 12-31-2021 NONE
123 01-01-2021 METAL
123 12-31-2021 METAL

From the example table above I would expect the query to return this

id date type
123 12-31-2021 METAL

If the table were to only contain types of “NONE” such as this example…

id date type
123 01-01-2021 NONE
123 12-31-2021 NONE
123 01-01-2021 NONE
123 12-31-2021 NONE

Then I would expect the result set to be..

id date type
123 12-31-2021 NONE

I’ve tried a plethora of different ways to do this but my current attempt looked something like thisIt works when there’s only one ID in the table but not for when I try to select a row for every specific ID in the table.


SELECT DISTINCT ON (id),
       date,
       type
FROM 
    example_table
WHERE
    CASE
        WHEN 
            (   SELECT 
                    COUNT(*) 
                FROM 
                    example_table t 
                WHERE 
                    t.type <> 'NONE'
                AND t.id = example_table.id) 
            <> 0
        THEN type <> 'NONE'
        ELSE 1=1
    END
ORDER BY 
    id, date DESC
Exit mobile version