CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Klick
Klick

Reputation: 1653

Postgresql - multiple select condition

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

Upvotes: 2

Views: 893

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 0

GMB
GMB

Reputation: 222462

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.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

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.

Upvotes: 2

Related Questions