Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select distinct value if only ONE distinct value exists?

Tags:

sql

sql-server

Using SQL Server 2012. I want to select a distinct value from a table T if only one distinct value exists. Below I have a simplified example of what I'm trying to do.

This data would result in 1 returned value: 'bbb'

ID    Data
----------
1    'bbb'

The same goes for this data:

ID    Data
----------
1    'bbb'
2    'bbb'
3    'bbb'

But this data would result in 0 returned rows:

ID    Data
----------
1    'aaa'
2    'bbb'
3    'bbb'

Ideally, there would be a way to count the number of rows in the WHERE clause (without grouping data). Also, please remember that my original query is quite complicated. For this simplified version, I tried something like this, but it doesn't work:

SELECT [Data] FROM
    (SELECT [Data], COUNT(*) OVER() AS [DinstinctValueCount] FROM
        (SELECT DISTINCT [Data] FROM [T]) [A]) [B] WHERE [DistinctValueCount] = 1

Invalid column name 'DistinctValueCount'.

UPDATE

I found a solution. Is there a better one?

SELECT CASE WHEN COUNT(*) = 1 THEN MIN([Data]) ELSE NULL END AS [Data] FROM
    (SELECT DISTINCT [Data] FROM [T]) [A]

Any aggregate function will do.

like image 328
l33t Avatar asked Jan 12 '23 03:01

l33t


2 Answers

SELECT MIN(ID), [Data]
FROM [tablename]
GROUP BY [Data]
HAVING
  1=(SELECT COUNT(DISTINCT [Data]) FROM [tablename])

or if you don't want to repeat [tablename], you could use something like this:

SELECT MIN(ID), MIN([Data])
FROM [tablename]
HAVING MIN([Data])=MAX([Data])

Please see fiddle here.

like image 110
fthiella Avatar answered Jan 22 '23 14:01

fthiella


Try something like this.

SELECT t1.* 
FROM tbl t1
JOIN ( 
  SELECT data
  FROM tbl t2
  GROUP BY data
  HAVING COUNT(1) = (SELECT COUNT(1) FROM tbl)
) t2 ON t2.data = t1.data
like image 21
T I Avatar answered Jan 22 '23 12:01

T I