2

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

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
1
  • 1
    Great, thanks a lot. It was actually Oracle, but I figured MINUS did the same thing thanks to you. I don't know why I was trying to overcomplicate the problem trying so many different things. I knew there had to be an easier solution.
    – DB Scrub
    May 12 '16 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.