Postgresql - multiple select condition

Question

I have table with following structure:

|id|author_id|name|type|created_at|updated_at

As a type I can have 5 different types, (A, B, C, D, E).

I need a query DB by author_id where I can select only last updated row "type" A and B. And select all other type rows.

So the result should be something like:

| id  | author_id | name  | type | created_at | updated_at
| 12  | 88        | lorem | A
| 45  | 88        | lorem | B
| 44  | 88        | lorem | C
| 154 | 88        | lorem | C
| 98  | 88        | lorem | C
| 856 | 88        | lorem | E
| 857 | 88        | lorem | E

Is it possible with single query? Or I need to use two queries?

Thank you

Tim Biegeleisen · Accepted Answer · 2020-02-10 16:25:33Z

2

You may try the following:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY "type" ORDER BY updated_at DESC) rn
    FROM yourTable
)

SELECT id, author_id, name, "type", created_at, updated_at
FROM cte
WHERE
    ("type" IN ('A', 'B') AND rn = 1) OR
    "type" NOT IN ('A', 'B');

This approach uses ROW_NUMBER to find the latest rows for all types. In the query on the CTE, we select only the most recently updated rows for types A and B, but we select all rows for all other types.

answered Feb 10, 2020 at 16:25

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Klick Over a year ago

Absolutely brilliant. Thank you.

GMB · Accepted Answer · 2020-02-10 16:29:40Z

0

Assuming that id is a unique key in the table, you could do this with distinct on:

select distinct on(case when type in ('A', 'B') then type else id::text end) t.*
from mytable t
order by case when type in ('A', 'B') then type else id::text end, created_at desc, id

This uses a conditional expression as distinct on key, that returns either type if it is A or B, or the id for other values. So you get the top 1 value for types A and B, and all other values for other types.

answered Feb 10, 2020 at 16:29

GMB

224k25 gold badges103 silver badges151 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2020-02-10 18:02:51Z

0

I am thinking:

(select distinct on (type) t.*
 from t
 where type in ('A', 'B')
 order by type, created_at desc
) union all
select t.*
from t
where type not in ('A', 'B');

In particular, this can make good use of an index on (type, created_at desc).

answered Feb 10, 2020 at 18:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgresql - multiple select condition

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related