Search code examples
sqloracle-databasesubquerywindow-functions

How to find rows with similar columns with the exception of a single column

I'm trying to find rows from the following table

T_Date     | Code  | TWS    
2018-01-01 | A0001 | TRDR001
2018-01-01 | A0001 | TRDR002
2018-01-02 | A0002 | TRDR001
2018-01-02 | A0003 | TRDR001
2018-01-03 | A0004 | TRDR001
2018-01-04 | A0004 | TRDR002
2018-01-05 | A0006 | TRDR001
2018-01-05 | A0006 | TRDR003

The expecting result is to find those rows which has different TWS on the same date of the same Code

T_Date     | Code  | TWS
2018-01-01 | A0001 | TRDR001
2018-01-01 | A0001 | TRDR002
2018-01-05 | A0006 | TRDR001
2018-01-05 | A0006 | TRDR003

I have tried some things to no avail.

select t.T_Date,t.Code,t.TWS
from trades t 
group by t.T_Date,t.Code,t.TWS
having COUNT(t.TWS)>1

Can anyone help me?

Solution

  • We could use the Exists operator:

    Select T_Date, Code, TWS
From table_name T
Where Exists(
  Select 1 From table_name D
  Where D.T_Date = T.T_Date And D.Code = T.Code And D.TWS <> T.TWS)
Order By T_Date, Code, TWS

    Demo