Select rows with same ID and AMOUNT

Question

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 ?

is the code or var2 unique for every identical pair of id and amount? — nbk, Jan 10 '23 at 15:32
Does this answer your question? [How to find records with duplicate values for some specific columns only in oracle PL/SQL](https://stackoverflow.com/questions/50820518/how-to-find-records-with-duplicate-values-for-some-specific-columns-only-in-orac) Or [How Do I Find Duplicates For Multiple Column Combinations in Oracle](https://stackoverflow.com/questions/72530220/how-do-i-find-duplicates-for-multiple-column-combinations-in-oracle) — astentx, Jan 10 '23 at 21:54

score 0 · Answer 1 · answered Jan 10 '23 at 15:40

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

score 0 · Answer 2 · answered Jan 10 '23 at 15:52

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

nbk · Accepted Answer · 2023-01-10T16:39:45.550

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

SQL Fiddle

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

Results:

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

Select rows with same ID and AMOUNT

3 Answers3