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

Question

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

GoonerForLife · Accepted Answer · 2021-12-23 04:40:29Z

2

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

edited Dec 23, 2021 at 4:40

answered Dec 23, 2021 at 4:34

GoonerForLife

7644 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

e92M3 Over a year ago

Thank you I like this. I would appreciate it if you could help me understand how that case statement works, specifically the THEN 1 ELSE 2 part in the order by clause. order by 1 order by 2? What is that doing?

GoonerForLife Over a year ago

Using the case statement, you are basically assigning weight to the types and then use the weight to order them.

GoonerForLife Over a year ago

You are assigning smaller number to the values other than 'None' so that they will come first when you order them.

e92M3 Over a year ago

Aha thanks. I was never aware you could use order by and assign weights like that.

rajorshi · Accepted Answer · 2021-12-23 09:38:42Z

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)

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related