Oracle row_number analysis functions simplify many of our operations

Source: Internet
Author: User

// 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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.