Search code examples
databasesqlitegroup-bymaxwindow-functions

How to get Column from Max of multi another columns?

I need to get the G value at that row contain max of max columns (H,J,J)

Below example: after group, max value of H or I or J is 170, so I need to get column value in column G is 06/25/2022 07:00:00.

I used the following query, it seems to work but returned a lot of missing values after GROUP

"Select C,MAX(MAX(H),MAX(I),MAX(J)) as d1,G GROUP BY C HAVING H=d1 OR I=d1 OR j=d1"

enter image description here

How do I fix this.

Solution

  • Use a CTE that returns the max of H, I and J for each C like this:

    WITH cte AS (
  SELECT C, MAX(MAX(H), MAX(I), MAX(J)) max
  FROM tablename
  GROUP BY C
)
SELECT t.C, t.G 
FROM tablename t
WHERE (t.c, MAX(t.H, t.I, t.J)) IN (SELECT C, max FROM cte);

    For your sample data, maybe it is more suitable to GROUP BY B.

    Or, with ROW_NUMBER() window function:

    WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY C ORDER BY MAX(H, I, J) DESC) rn
  FROM tablename
)
SELECT C, G 
FROM cte
WHERE rn = 1;

    Or, with FIRST_VALUE() window fuction:

    SELECT DISTINCT C, 
       FIRST_VALUE(G) OVER (PARTITION BY C ORDER BY MAX(H, I, J) DESC) G
FROM tablename;

    See the demo.