Home » SQL & PL/SQL » SQL & PL/SQL » return top 10 records based on dynamic count of prev records results (oracle 11)
return top 10 records based on dynamic count of prev records results [message #670826] Sun, 29 July 2018 04:09 Go to next message
greenwaldliron
Messages: 4
Registered: July 2018
Junior Member
Hi,

I have an ordered table of products and customers as follows:

rn product customer
1 859274 A
2 859267 A
3 859250 A
4 863592 B
5 862250 B
6 862700 B
7 862694 B
8 862120 B
9 863592 C
10 862250 C
11 862120 D
12 862694 C
13 863592 E
14 862113 D
15 863592 F
16 862250 F
17 862700 E
18 862694 E
19 863592 G


I need an SQL sentence (not using pl/sql block) that will return the top 10 product/customer pairs HOWEVER both product and customer cannot repeat themself more than 2 times.

to make it clearer row 3 is out because customer A was included twice before.
row 13 is out (although first time this customer appears) because product 863592 was already included 2 times in vetted records.

the answer for the above example should be:

1 859274 A
2 859267 A
4 863592 B
5 862250 B
9 863592 C
10 862250 C
11 862120 D
14 862113 D
17 862700 E
18 862694 E

I tried several options like analytic functions including cumulative counts, lag(), etc... was not able to solve this one Smile

Thanks
Re: return top 10 records based on dynamic count of prev records results [message #670827 is a reply to message #670826] Sun, 29 July 2018 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66682
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: return top 10 records based on dynamic count of prev records results [message #670828 is a reply to message #670827] Sun, 29 July 2018 06:27 Go to previous messageGo to next message
greenwaldliron
Messages: 4
Registered: July 2018
Junior Member
here you go:

version: 11.2.0.1.0

create table tbl_test (rn number, product_id number,customer_id varchar2(10));


insert into tbl_test(rn,product_id,customer_id)values(1, 859274, 'A' );
insert into tbl_test(rn,product_id,customer_id)values(2, 859267, 'A');
insert into tbl_test(rn,product_id,customer_id)values(3, 859250, 'A');
insert into tbl_test(rn,product_id,customer_id)values(4, 863592, 'B' );
insert into tbl_test(rn,product_id,customer_id)values(5, 862250, 'B' );
insert into tbl_test(rn,product_id,customer_id)values(6, 862700, 'B' );
insert into tbl_test(rn,product_id,customer_id)values(7, 862694, 'B' );
insert into tbl_test(rn,product_id,customer_id)values(8, 862120, 'B' );
insert into tbl_test(rn,product_id,customer_id)values(9, 863592, 'C' );
insert into tbl_test(rn,product_id,customer_id)values(10, 862250, 'C' );
insert into tbl_test(rn,product_id,customer_id)values(11, 862120, 'D' );
insert into tbl_test(rn,product_id,customer_id)values(12, 862694, 'C' );
insert into tbl_test(rn,product_id,customer_id)values(13, 863592, 'E' );
insert into tbl_test(rn,product_id,customer_id)values(14, 862113, 'D' );
insert into tbl_test(rn,product_id,customer_id)values(15, 863592, 'F' );
insert into tbl_test(rn,product_id,customer_id)values(16, 862250, 'F') ;
insert into tbl_test(rn,product_id,customer_id)values(17, 862700, 'E') ;
insert into tbl_test(rn,product_id,customer_id)values(18, 862694, 'E') ;
insert into tbl_test(rn,product_id,customer_id)values(19, 863592, 'G') ;
commit;



I'm looking for an SQL sentence (not using pl/sql block) that will return the top 10 product/customer pairs HOWEVER both product and customer cannot repeat themself more than 2 times.

to make it clearer row 3 is out because customer A was included twice before.
row 13 is out (although first time this customer appears) because product 863592 was already included 2 times in vetted records.

the answer for the above example should be:

RN, PRODUCT_ID, CUSTOMER_ID
1, 859274, A
2, 859267, A
4, 863592, B
5, 862250, B
9, 863592, C
10, 862250, C
11, 862120, D
14, 862113, D
17, 862700, E
18, 862694, E

Thanks

[Updated on: Sun, 29 July 2018 06:28]

Report message to a moderator

Re: return top 10 records based on dynamic count of prev records results [message #670830 is a reply to message #670826] Sun, 29 July 2018 07:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2885
Registered: January 2010
Location: Connecticut, USA
Senior Member
greenwaldliron wrote on Sun, 29 July 2018 05:09
I tried several options like analytic functions including cumulative counts, lag(), etc... was not able to solve this one Smile
Analytic functions will not help here since you have recursive formula. Use MODEL:

WITH T AS (
           SELECT  *
             FROM  TBL_TEST
             MODEL
               DIMENSION BY(
                            RN,
                            PRODUCT,
                            CUSTOMER
                           )
               MEASURES(1 FLAG)
               RULES(
                     FLAG[RN,ANY,ANY] ORDER BY RN = CASE
                                                      WHEN COUNT(FLAG)[RN <= CV(RN),ANY,CUSTOMER = CV(CUSTOMER)] > 2 THEN NULL
                                                      WHEN COUNT(FLAG)[RN <= CV(RN),PRODUCT = CV(PRODUCT),ANY] > 2 THEN NULL
                                                      ELSE COUNT(FLAG)[RN <= CV(RN),ANY,ANY]
                                                    END
                    )
          )
SELECT  RN,
        PRODUCT,
        CUSTOMER
  FROM  T
  WHERE FLAG <= 10
  ORDER BY RN
/

        RN    PRODUCT CUSTOMER
---------- ---------- --------
         1     859274 A
         2     859267 A
         4     863592 B
         5     862250 B
         9     863592 C
        10     862250 C
        11     862120 D
        14     862113 D
        17     862700 E
        18     862694 E

10 rows selected.

SQL> 

SY.

[Updated on: Sun, 29 July 2018 07:39]

Report message to a moderator

Re: return top 10 records based on dynamic count of prev records results [message #670831 is a reply to message #670830] Sun, 29 July 2018 07:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2885
Registered: January 2010
Location: Connecticut, USA
Senior Member
Solution I posted ranks (column flag) all rows and then selects first 10. You could make it more efficient (exit after 10 rows are found) if you change MODEL to iterative.

SY.
Re: return top 10 records based on dynamic count of prev records results [message #670834 is a reply to message #670828] Sun, 29 July 2018 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 66682
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why row 18 is not out for the same reason than row 13: appears in rows 7 and 12?

[Updated on: Sun, 29 July 2018 08:24]

Report message to a moderator

Re: return top 10 records based on dynamic count of prev records results [message #670836 is a reply to message #670834] Sun, 29 July 2018 09:12 Go to previous messageGo to next message
greenwaldliron
Messages: 4
Registered: July 2018
Junior Member
because rows 7 and 12 should not be counted as they were not satisfying the rule (no more than 2 product and no more than 2 customers...)
Re: return top 10 records based on dynamic count of prev records results [message #670838 is a reply to message #670830] Sun, 29 July 2018 09:23 Go to previous message
greenwaldliron
Messages: 4
Registered: July 2018
Junior Member
Thanks Solomon this SQL looks amazing Smile
Previous Topic: Possibility of Name
Next Topic: Performance of Stored Programs
Goto Forum:
  


Current Time: Wed Nov 13 05:38:37 CST 2019