Ufdjrw

Given a table A, columns in the following order (item_id, order_id, product_id) set up like so:

1 1 2
2 2 4
3 3 3
4 3 6
5 4 2
6 5 5
7 6 5
8 7 1
9 7 7
10 7 9
11 8 10
12 9 1
13 5 7
14 7 5
15 6 7
16 1 1
17 4 1
18 8 3
19 3 10

Select the top three pairs of product_ids with same order_id using only oracle sql. I have tried self joins, subqueries and everything else but cannot seem to get anything working... If someone can get this you will be my programming idol.

So the output would be

Most occurred pair: 7 and 5 because they occur together three times second most occurred: 3,10 because they occur together twice third most occurred: 1,2 because they occur together twice

So, logically, going down the list when order_id = 1 the product_ids = 1 and 2 (so one occurrence) they occur together again when order_id = 4 When order_id = 5,6, or 7 they have 5 and 7 as product_id's so they occur 3 times... and so on

Two different queries (one hierarchical and the other with a self join) - both give the same answer (go for whichever is more performant):

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE A (item_id, order_id, product_id) AS
 SELECT 1, 1, 2 FROM DUAL
UNION ALL SELECT 2, 2, 4 FROM DUAL
UNION ALL SELECT 3, 3, 3 FROM DUAL
UNION ALL SELECT 4, 3, 6 FROM DUAL
UNION ALL SELECT 5, 4, 2 FROM DUAL
UNION ALL SELECT 6, 5, 5 FROM DUAL
UNION ALL SELECT 7, 6, 5 FROM DUAL
UNION ALL SELECT 8, 7, 1 FROM DUAL
UNION ALL SELECT 9, 7, 7 FROM DUAL
UNION ALL SELECT 10, 7, 9 FROM DUAL
UNION ALL SELECT 11, 8, 10 FROM DUAL
UNION ALL SELECT 12, 9, 1 FROM DUAL
UNION ALL SELECT 13, 5, 7 FROM DUAL
UNION ALL SELECT 14, 7, 5 FROM DUAL
UNION ALL SELECT 15, 6, 7 FROM DUAL
UNION ALL SELECT 16, 1, 1 FROM DUAL
UNION ALL SELECT 17, 4, 1 FROM DUAL
UNION ALL SELECT 18, 8, 3 FROM DUAL
UNION ALL SELECT 19, 3, 10 FROM DUAL

Query 1:

SELECT *
FROM (
 SELECT lower_product_id,
 upper_product_id,
 COUNT(1) AS frequency,
 LISTAGG( order_id, ', ' ) WITHIN GROUP ( ORDER BY order_id ) AS order_ids
 FROM (
 SELECT order_id,
 lower_product_id,
 upper_product_id
 FROM (
 SELECT order_id,
 PRIOR product_id AS lower_product_id,
 product_id AS upper_product_id,
 LEVEL AS lvl
 FROM A
 CONNECT BY
 PRIOR order_id = order_id
 AND PRIOR product_id < product_id
 )
 WHERE lvl = 2
 )
 GROUP BY lower_product_id, upper_product_id
 ORDER BY frequency DESC, lower_product_id, upper_product_id
)
WHERE ROWNUM <= 3

Results:

| LOWER_PRODUCT_ID | UPPER_PRODUCT_ID | FREQUENCY | ORDER_IDS |
|------------------|------------------|-----------|-----------|
| 5 | 7 | 3 | 5, 6, 7 |
| 1 | 2 | 2 | 1, 4 |
| 3 | 10 | 2 | 3, 8 |

Query 2:

SELECT *
FROM (
 SELECT A.product_id AS lower_product_id,
 B.product_id AS upper_product_id,
 COUNT(1) AS frequency,
 LISTAGG( A.order_id, ', ' ) WITHIN GROUP ( ORDER BY A.order_id ) AS order_ids
 FROM A
 INNER JOIN A B
 ON ( A.order_id = B.order_id
 AND A.product_id < B.product_id )
 GROUP BY A.product_id, B.product_id
 ORDER BY frequency DESC, lower_product_id, upper_product_id
)
WHERE ROWNUM <= 3

Results:

| LOWER_PRODUCT_ID | UPPER_PRODUCT_ID | FREQUENCY | ORDER_IDS |
|------------------|------------------|-----------|-----------|
| 5 | 7 | 3 | 5, 6, 7 |
| 1 | 2 | 2 | 1, 4 |
| 3 | 10 | 2 | 3, 8 |

This could be what you want :

select item_id,order_id,product_id,
(case when order_id = product_id then 'Yes' else 'No' end) as pairs
from tableA
having pairs = 'Yes'
order by order_id asc
limit 3

or without using HAVING clause

select item_id,order_id,product_id
from tableA
where order_id = product_id
order by order_id asc
limit 3

If that's what you want to achieve,you can try this:

 select product_id,myranking from 
 (select product_id, dense_rank() over(order by mycount desc) myranking from 
 (select product_id,count(*) over (partition by product_id) mycount from mytable)) 
 where myranking<4 group by product_id,myranking

the output will be order_id with the most occurences at the product_id column and its rank