i have 2 columns in a table. Data looks like this
Folio_no | Flag 1145 R 201 S 1145 FR 300 E 1145 R 201 E 201 S
Expected Output: Folio_No | Flag 1145 R 201 S 300 E
The output should give the folio_no along with the flag which occured maximum number of times for that particular folio number.
i tried doing the below but it throws an error
select folio_no, max(count(flag)) from table group by folio_no;
Advertisement
Answer
We can use an aggregation:
WITH cte AS ( SELECT Folio_No, Flag, COUNT(*) AS cnt FROM yourTable GROUP BY Folio_No, Flag ), cte2 AS ( SELECT t.*, RANK() OVER (PARTITION BY Folio_No ORDER BY cnt DESC, Flag) rnk FROM cte t ) SELECT Folio_No, Flag FROM cte2 WHERE rnk = 1;
Note that I assume should two flags within a given folio number be tied for the max frequency, that you want to report the earlier flag.
Here is a working demo.