Oracle SQL – Return One Row from Table with Similar Values

oracleplsql

I want to run a query from a table with similar values in the same column that match a certain criteria, but only return 1 row per similar value.

Let's say I have this simplified table:

Column1   Column2
  A           1
  A           2
  A           3
  A           4
  B           1
  B           2
  C           1
  C           3
  C           4
  D           2

I want to say, for example, return when Column 2 is not 3, and the result should be:

 B
 D

Best Answer

You can use the EXCEPT keyword:

CREATE TABLE #T1(c1 char(1), c2 int)
go

INSERT INTO #T1 Values('A',1)
INSERT INTO #T1 Values('A',2)
INSERT INTO #T1 Values('A',3)
INSERT INTO #T1 Values('A',4)
INSERT INTO #T1 Values('B',1)
INSERT INTO #T1 Values('B',2)
INSERT INTO #T1 Values('C',1)
INSERT INTO #T1 Values('C',3)
INSERT INTO #T1 Values('C',4)
INSERT INTO #T1 Values('D',2)
GO

SELECT c1 FROM  #T1
EXCEPT 
SELECT c1 FROM #t1 WHERE c2 = 3

DROP table #T1
Related Question