0

Tabls is

ID  Count
1    30
2    30
3    10
4    15
5    10
6    25

I want query which will give me 

4    15
6    25

in result

user3260664
  • 145
  • 1
  • 3
  • 9

4 Answers4

3

You can use NOT EXISTS:

SELECT ID,  Count
FROM dbo.TableName t1
WHERE NOT EXISTS
(
   SELECT 1 FROM dbo.TableName t2
   WHERE t1.ID <> t2.ID AND t1.Count = t2.Count
)

Demo

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Please note that here also you should have an index on `Count`. – Thorsten Dittmar May 08 '14 at 12:49
  • @ThorstenDittmar: mentioned that an index would be helpful. However, imho that depends on if this query is used only once or often and how large the table is at all. – Tim Schmelter May 08 '14 at 12:56
  • I mentioned it in my answer - who knows whether it's being read :-) Yes, it's a matter of how often this is done, but can it hurt to do it any case? – Thorsten Dittmar May 08 '14 at 12:58
  • Thanks, Tim, I know that - but it's a simple table with probably the PK being on ID. I know that many indices with many columns slow things down significantly, but here I guess it speeds up lookup more than it slows down inserts. – Thorsten Dittmar May 08 '14 at 13:03
  • @ThorstenDittmar: sorry, i've just deleted my link-only comment. For the sake of completeness, it was this: http://stackoverflow.com/a/764217/284240 My point is, if i would add an index on every column that i need to query somewhere i would have to add an index on everything. – Tim Schmelter May 08 '14 at 13:03
  • Well, if you already have an index that restricts the number of rows significantly before doing the "unindexed search" I agree, but here it comes down to a full table scan without an index - and full table scans are hardly ever desired... – Thorsten Dittmar May 08 '14 at 13:12
1

The following should select what you want: 

SELECT t.ID, t.[Count] 
FROM Table t 
WHERE 
   (SELECT COUNT(*) FROM Table t1 WHERE t1.[Count] = t.[Count]) = 1

Please note that you should really have an index on Table.[Count].

Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139
1

you could also do it with a grouping statement

SELECT MIN(ID), Count
FROM Table
GROUP BY Count
HAVING COUNT(*) = 1
Jaloopa
  • 722
  • 1
  • 6
  • 21
0

Use HAVING together with COUNT DISTINCT, to limit the result:

SELECT [Id], [Count]
FROM MyTable
WHERE [Count] IN (
    SELECT [Count]
    FROM MyTable
    GROUP BY [Count]
    HAVING COUNT(DISTINCT [Count]) = 1
)
Dan
  • 10,480
  • 23
  • 49