How to find the highest and second highest entry in SQL in a single query using `GROUP BY`?-CodePudding

Let this be the table that is provided.

PID	TID	Type	Freq
1	1	A	3
1	1	A	2
1	1	A	1
1	1	B	3
1	2	A	4
1	2	B	5

I want to write a query to get an output like this.

PID	TID	Type	Max_Freq_1	Max_Freq_2
1	1	A	3	2
1	1	B	3	NULL
1	2	A	4	NULL
1	2	B	5	NULL

That is, given a combination of PID, TID, Type, what is the highest and second-highest frequency? If there aren't a sufficient number of entries in the table, then put second highest as NULL

CodePudding user response：

This is what I came up with on postgresQL. Using window function like row_number is the easiest way to get the result you want.

with t as (
    select *, row_number() over (partition by pid, tid, "type" order by freq desc) as r 
    from test_so 
) select pid, tid, "type", max(case when r = 1 then freq end) as "highest", max(case when r = 2 then freq end) as "second_highest" 
    from t 
    group by pid, tid, "type"

postgres resultset image

CodePudding user response：

If your database can use the window functions, then the top 2 Freq can be calculated via the DENSE_RANK function.

SELECT PID, TID, Type
, MAX(CASE WHEN Rnk = 1 THEN Freq END) AS Max_Freq_1
, MAX(CASE WHEN Rnk = 2 THEN Freq END) AS Max_Freq_2
FROM
(
    SELECT PID, TID, Type, Freq
    , DENSE_RANK() OVER (PARTITION BY PID, TID, Type ORDER BY Freq DESC) AS Rnk
    FROM YourTable t
) q
GROUP BY PID, TID, Type
ORDER BY PID, TID, Type

pid	tid	type	max_freq_1	max_freq_2
1	1	A	3	2
1	1	B	3	null
1	2	A	4	null
1	2	B	5	null

If ROW_NUMBER isn't available, then try this.

SELECT PID, TID, Type
, MAX(CASE WHEN Rnk = 1 THEN Freq END) AS Max_Freq_1
, MAX(CASE WHEN Rnk = 2 THEN Freq END) AS Max_Freq_2
FROM
(
    SELECT t1.PID, t1.TID, t1.Type, t1.Freq
    , COUNT(DISTINCT t2.Freq) AS Rnk
    FROM YourTable t1
    LEFT JOIN YourTable t2 
      ON t2.PID = t1.PID
     AND t2.TID = t1.TID
     AND t2.Type = t1.Type
     AND t2.Freq >= t1.Freq
    GROUP BY t1.PID, t1.TID, t1.Type, t1.Freq
) q
GROUP BY PID, TID, Type
ORDER BY PID, TID, Type

Demo on db<>fiddle here