SQL : Max value based on multiple row values

Question

Based on my table :

Client  Tier    Value
A        1       10
B        2       20
C        1       30
B        3       40
A        2       40
B        1       50
C        2       60
A        3       70
A        4       50

I am trying to get the following output :

Client  max(1,2) Value  max(3,4) Value
A   40          70
B   50          40

Using Python I can easily do , But I am new to Postgres. So Basically for every client, I need to get Maximum of (Tier 1 Value, Tier 2 Value) as a first column and Maximum of (Tier 3 Value, Tier 4 Value) as a second column.

Answer 1

We can use conditional aggregation here:

SELECT Client,
       MAX(CASE WHEN Tier IN (1, 2) THEN Value END) AS max_1_2,
       MAX(CASE WHEN Tier IN (3, 4) THEN Value END) AS max_3_4
FROM yourTable
GROUP BY Client;

Or, using the FILTER clause, which Postgres supports in recent versions, we could also write this as:

SELECT Client,
       MAX(Value) FILTER (WHERE Tier IN (1, 2)) AS max_1_2,
       MAX(Value) FILTER (WHERE Tier IN (3, 4)) AS max_3_4
FROM yourTable
GROUP BY Client;