CODEWITHSUNDEEP
sqldatabasepostgresql
e92M3
e92M3

Reputation: 85

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 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.

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 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

Upvotes: 0

Views: 2530

Answers (2)

rajorshi
rajorshi

Reputation: 717

Without using Analytical function: SQL:

with cte
as
(
 select id,type,flag, max(date) date from (select id , date , type, case when type='NONE' then 'flag_none' else 'flag_not_none' end as flag from test) x group by  id,type,flag)
select id,type,date from cte where flag='flag_not_none'  and  (id,date) in (select id,max(date) from cte group by id)
union
select id,type,date from cte where id not in (select id from cte where flag='flag_not_none'  and  (id,date) in (select id,max(date) from cte group by id)) and flag='flag_none';

Full Execution:

CREATE TABLE test 
(

  id int,
  date date,
  type text
);

insert into test
values
(123,  '01-01-2021', 'NONE'),
(123, '12-31-2021', 'NONE'),
(123, '01-01-2021', 'METAL'),
(123, '12-31-2021', 'PLASTIC'),
(124,  '01-01-2021', 'NONE'),
(124, '12-31-2021', 'NONE'),
(124, '01-01-2021', 'NONE'),
(124, '12-31-2021', 'NONE'),
(125, '12-25-2021', 'NONE'),
(125, '12-25-2021', 'RUBBER'),
(125, '12-31-2021', 'STEEL');

postgres=# with cte
postgres-# as
postgres-# (
postgres(#  select id,type,flag, max(date) date from (select id , date , type, case when type='NONE' then 'flag_none' else 'flag_not_none' end as flag from test) x group by  id,type,flag)
postgres-# select id,type,date from cte where flag='flag_not_none'  and  (id,date) in (select id,max(date) from cte group by id)
postgres-# union
postgres-# select id,type,date from cte where id not in (select id from cte where flag='flag_not_none'  and  (id,date) in (select id,max(date) from cte group by id)) and flag='flag_none';
 id  |  type   |    date
-----+---------+------------
 123 | PLASTIC | 2021-12-31
 124 | NONE    | 2021-12-31
 125 | STEEL   | 2021-12-31
(3 rows)

Upvotes: 1

GoonerForLife
GoonerForLife

Reputation: 754

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

Upvotes: 2

Related Questions