Here is an example of the table I am working with. What 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.

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

If the table were to only contain types of "NONE" such as this example...

Then I would expect the result set to be..

I've tried a plethora of different ways to do this but my current attempt looked something like this. It 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

 

CodePudding user response：

You can use Row_number() function together with a case statement to identify which row to pick.

with cte AS
(
  select id,
       date, 
       type,
       row_number() over(partition by id 
                          order by case when type <> 'NONE' THEN 1 ELSE 2 END, date desc
                          ) as RN
       
  from test
 )
 select *
 from cte
 where rn = 1

SQL Fiddle