Home » SQL & PL/SQL » SQL & PL/SQL » return top 10 records based on dynamic count of prev records results (oracle 11)
|
|
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 |
|
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 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
greenwaldliron wrote on Sun, 29 July 2018 05:09I tried several options like analytic functions including cumulative counts, lag(), etc... was not able to solve this one
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
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 17:24:46 CDT 2024
|