Oracle row_number analysis functions simplify many of our operations

// Problem:
CI MSISDN CNT
18802 13009060195
18830 13009060195 1
18837 13009060195 2
18829 13009063333 5
18830 13009063333 7
18847 13009063333 4
// Result:
CI MSISDN CNT
18802 13009060195
18830 13009063333 7
// This problem is to query the cnt of the same msisdn item.
// When this problem occurs, I think so:
// First, query the cnt of the same msisdn item to form a result set:
With t (
Select '000000' ci, '000000' msisdn, 9 cnt from dual union all
Select '200', '200', 1 from dual union all
Select '200', '200', 2 from dual union all
Select '200', '200', 5 from dual union all
Select '200', '200', 7 from dual union all
Select '123', '123', 4 from dual)
Select msisdn, max (cnt) cnt
From t
Group by msisdn
--
MSISDN CNT
---------------------
13009060195 9
13009063333 7
// Second, match the original table with the structure set. If both MSISDN and CNT are the same, the table is extracted.
Select t .*
From t ,(
Select msisdn, max (cnt) cnt
From t
Group by msisdn
) B
Where t. msisdn = B. msisdn and
T. cnt = B. cnt;
// In this way, we can get the expected results as follows:
CI MSISDN CNT
--------------------------
18802 13009060195
18830 13009063333 7
// Analysis:
// This is not a difficult problem,
/********* Group statistics ******/
// But this is a subquery and a table connection. Why bother?
// There are certainly better solutions:
Select ci, msisdn, cnt
From (select ci,
Msisdn,
Cnt,
Row_number () over (partition by msisdn order by cnt desc) rn from t)
Where rn = 1;