I am struggling with a problem that i'm trying to solve on a ORACLE DB 19.0.0.0.0
I have a TABLE like this:
ID | CODE | AMOUNT | VAR1 | VAR2
-------------------------------
10 | 255 |192.50 |CLOSED | 1
10 | 342 |192.50 |OPEN | 7
10 | 921 |255.00 |RUNNING| 2
10 | 222 |255.00 |CLOSED | 10
13 | 101 |10.00 |RUNNING| 3
13 | 122 |19.25 |RUNNING| 7
71 | 804 |21.25 |OPEN | 9
71 | 744 |21.25 |RUNNING| 2
13 | 100 |950.00 |OPEN | 10
90 | 126 |17.80 |RUNNING| 0
90 | 137 |9.00 |RUNNING| 0
And i need to select rows that have the same ID and same AMOUNT. they can form pairs, trios or more, In other words, i need to discard IDs and AMOUNTs that are unique. The others columns can have any value
I expect to return:
ID | CODE | AMOUNT | VAR1 | VAR2
-------------------------------
10 | 255 |192.50 |CLOSED | 1
10 | 342 |192.50 |OPEN | 7
10 | 921 |255.00 |RUNNING| 2
10 | 222 |255.00 |CLOSED | 10
71 | 804 |21.25 |OPEN | 9
71 | 744 |21.25 |RUNNING| 2
I already tried doing a query that return only the unique ID AMOUNT rows, creating an "UNIQUE KEYS TABLE", and then a JOIN or a WHERE in the TABLE, but in my case it is not efficient enough
What is the best and efficient way to solve this ?
CodePudding user response：
WITH CTE(ID ,CODE , AMOUNT , VAR1 , VAR2)AS
(
SELECT 10 , 255 ,192.50 ,'CLOSED' , 1 UNION ALL
SELECT 10 , 342 ,192.50 ,'OPEN' , 7 UNION ALL
SELECT 10 , 921 ,255.00 ,'RUNNING', 2 UNION ALL
SELECT 10 , 222 ,255.00 ,'CLOSED' , 10 UNION ALL
SELECT 13 , 101 ,10.00 ,'RUNNING', 3 UNION ALL
SELECT 13 , 122 ,19.25 ,'RUNNING', 7 UNION ALL
SELECT 71 , 804 ,21.25 ,'OPEN' , 9 UNION ALL
SELECT 71 , 744 ,21.25 ,'RUNNING', 2 UNION ALL
SELECT 13 , 100 ,950.00 ,'OPEN' , 10 UNION ALL
SELECT 90 , 126 ,17.80 ,'RUNNING', 0 UNION ALL
SELECT 90 , 137 ,9.00 ,'RUNNING', 0
)
SELECT C.ID,C.CODE,C.AMOUNT,C.VAR1,C.VAR2
FROM CTE C
JOIN
(
SELECT X.ID,X.AMOUNT
FROM CTE X
GROUP BY X.ID,X.AMOUNT
HAVING COUNT(*)>1
)ZZ ON C.ID=ZZ.ID AND C.AMOUNT=ZZ.AMOUNT
Sorry, today dbfiddle for Oracle does not work for me, so solution tested on MS SQL Server
CodePudding user response：
We can use a subquery to fetch the rows having same id and amount and then use IN in an outer query to add the remaining columns:
SELECT id, code, amount, var1, var2
FROM yourtable
WHERE (id, amount)
IN
(SELECT id, amount
FROM yourtable
GROUP BY id, amount
HAVING COUNT(*)>1)
ORDER BY id;
Tested with your sample data here: db<>fiddle
CodePudding user response：
You should add an INDEX on all three columns, whichmake the query much faster.
On bigger databases INNER JOIN is faster than an IN clause
Oracle 11g R2 Schema Setup:
CREATE TABLE Table1
("ID" int, "CODE" int, "AMOUNT" int, "VAR1" varchar2(7), "VAR2" int)
;
CREATE INDEX tab1_index_address
ON Table1("ID","CODE","AMOUNT");
INSERT ALL
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (10, 255, 192.50, 'CLOSED', 1)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (10, 342, 192.50, 'OPEN', 7)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (10, 921, 255.00, 'RUNNING', 2)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (10, 222, 255.00, 'CLOSED', 10)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (13, 101, 10.00, 'RUNNING', 3)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (13, 122, 19.25, 'RUNNING', 7)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (71, 804, 21.25, 'OPEN', 9)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (71, 744, 21.25, 'RUNNING', 2)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (13, 100, 950.00, 'OPEN', 10)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (90, 126, 17.80, 'RUNNING', 0)
INTO Table1 ("ID", "CODE", "AMOUNT", "VAR1", "VAR2")
VALUES (90, 137, 9.00, 'RUNNING', 0)
SELECT * FROM dual;
Query 1:
SELECT tab1.*
FROM Table1 tab1 INNER JOIN
Table1 tab2 ON tab1.ID = tab2.ID AND tab1."AMOUNT" = tab2."AMOUNT"
AND tab1.CODE <> tab2.CODE
| ID | CODE | AMOUNT | VAR1 | VAR2 |
|----|------|--------|---------|------|
| 10 | 921 | 255 | RUNNING | 2 |
| 10 | 342 | 193 | OPEN | 7 |
| 10 | 255 | 193 | CLOSED | 1 |
| 10 | 222 | 255 | CLOSED | 10 |
| 71 | 804 | 21 | OPEN | 9 |
| 71 | 744 | 21 | RUNNING | 2 |