SQL : Max value based on multiple row values

February 2, 2023

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.

>Solution :

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;