Sql-server – Distinct in one column where another column has same values

distinctsql server

I have a table sample

  id   |  name  | type
-------+--------+------
 PR001 | DC001  |    a
 PR002 | DC001  |    a
 PR003 | DC001  |    b
 PR004 | DC002  |    a
 PR005 | DC003  |    a
 PR006 | DC003  |    a
 PR007 | DC004  |    b
 PR008 | DC005  |    c

Is there a way to display distinct for column name only if where column type should be all the same value ('a')

output I want to achieve

 name   | type
---------+------
 DC002  |    a
 DC003  |    a

Any help would be greatly appreciated

Best Answer

SELECT name, MIN(type) AS type 
FROM table_name 
GROUP BY name
HAVING COUNT(DISTINCT type) = 1 ;

or the same with:

HAVING MIN(type) = MAX(type)

I assume that type is not nullable.

If you want only those that have type = 'a', use:

HAVING MIN(type) = 'a'
   AND MAX(type) = 'a' ;

Related Solutions

Sql-server – Using the name of a table contained in a column of another table

The only way to do this is to query SourceID in to a variable and then query the source table. At that point, you're better off pulling both into variablesm like this:

DECLARE @SourceTable NVARCHAR(255) --Note the length is arbitrary
,@SourceID NVARCHAR(255) --Assuming it's an INT, it doesn't matter
,@sql NVARCHAR(MAX)

SELECT @SourceTable = SourceID
,@SourceID = ReferenceID
FROM MyTable
WHERE SourceID = 'some_table'
AND ReferenceID = 1

SET @SQL = 'SELECT * FROM ' + @SourceTable + ' WHERE InstanceID = ' + @SourceID

EXEC(@sql)

You want the SourceID to be a string because otherwise you'd need to convert it to one anyway.

Sql-server – Select rows with same id but null and some other value in another column for that id

You should be able to use conditional aggregation to get the username with both a value in col2 as well as null.

I'd suggest using a HAVING clause with the conditions. The query would be similar to:

select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
  and sum(case when col2 is null then 1 else 0 end) = 1

See SQL Fiddle with Demo. This query groups your data by each username and then uses conditional logic to check if col2 meets both conditions you want - where col2 is not null and col2 is null.

You can then use this in a subquery, etc to get the username and col2 values:

select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) = 1
    and sum(case when col2 is null then 1 else 0 end) = 1
) d
  on t.username = d.username

See SQL Fiddle with Demo.

If you have more than one col2 row with both null and another value, then you just need to alter the HAVING clause slightly:

select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) >= 1
    and sum(case when col2 is null then 1 else 0 end) >= 1
) d
  on t.username = d.username;

See SQL Fiddle with Demo

Best Answer

Related Solutions

Sql-server – Using the name of a table contained in a column of another table

Sql-server – Select rows with same id but null and some other value in another column for that id

Related Question