Sql-server – SQL: How to SELECT only the rows with a unique value on certain column

sql server

Thanks a million everyone for everyone's response. Unfortunately, none of the solutions appear to be working on my end, and my guess is that the example I've provided is messed up.

So let me try again.

My table looks like this:

    contract    project activity
row1    1000    8000    10
row2    1000    8000    20
row3    1000    8001    10
row4    2000    9000    49
row5    2000    9001    49
row6    3000    9000    79
row7    3000    9000    78

Basically, the query I'm looking for would return "2000,49" for "contract, activity" because only contract #2000 has one, and ONLY one, unique activity value.

Again, thanks a million in advance,
boroatel

Best Answer

Updated to use your newly provided data:

The solutions using the original data may be found at the end of this answer.

Using your new data:

DECLARE  @T TABLE( [contract] INT, project INT, activity INT )
INSERT INTO @T VALUES( 1000,    8000,    10 )
INSERT INTO @T VALUES( 1000,    8000,    20 )
INSERT INTO @T VALUES( 1000,    8001,    10 )
INSERT INTO @T VALUES( 2000,    9000,    49 )
INSERT INTO @T VALUES( 2000,    9001,    49 )
INSERT INTO @T VALUES( 3000,    9000,    79 )
INSERT INTO @T VALUES( 3000,    9000,    78 )

SELECT DISTINCT [contract], activity FROM @T AS A WHERE
    (SELECT COUNT( DISTINCT activity ) 
     FROM @T AS B WHERE B.[contract] = A.[contract]) = 1

returns: 2000, 49

Solutions using original data

WARNING: The following solutions use the data previously given in the question and may not make sense for the current question. I have left them attached for completeness only.

SELECT Col1, Count( col1 ) AS count FROM table 
GROUP BY col1
HAVING count > 1

This should get you a list of all the values in col1 that are not distinct. You can place this in a table var or temp table and join against it.

Here is an example using a sub-query:

DECLARE @t TABLE( col1 VARCHAR(1), col2 VARCHAR(1), col3 VARCHAR(1) )

INSERT INTO @t VALUES( 'A', 'B', 'C' );
INSERT INTO @t VALUES( 'D', 'E', 'F' );
INSERT INTO @t VALUES( 'A', 'J', 'K' );
INSERT INTO @t VALUES( 'G', 'H', 'H' );

SELECT * FROM @t

SELECT col1, col2 FROM @t WHERE col1 NOT IN 
    (SELECT col1 FROM @t AS t GROUP BY col1 HAVING COUNT( col1 ) > 1)

This returns:

D   E
G   H

And another method that users a temp table and join:

DECLARE @t TABLE( col1 VARCHAR(1), col2 VARCHAR(1), col3 VARCHAR(1) )

INSERT INTO @t VALUES( 'A', 'B', 'C' );
INSERT INTO @t VALUES( 'D', 'E', 'F' );
INSERT INTO @t VALUES( 'A', 'J', 'K' );
INSERT INTO @t VALUES( 'G', 'H', 'H' );

SELECT * FROM @t

DROP TABLE #temp_table  
SELECT col1 INTO #temp_table
    FROM @t AS t GROUP BY col1 HAVING COUNT( col1 ) = 1

SELECT t.col1, t.col2 FROM @t AS t
    INNER JOIN #temp_table AS tt ON t.col1 = tt.col1

Also returns:

D   E
G   H