SQL: How do I SELECT only the rows with a unique value on certain column?

Question

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

What is the PK for this table? – Walter Mitty Jun 14 '11 at 13:43 — Walter Mitty, Jun 14 '11 at 13:43

vfilby · Answer 1 · 2008-10-22T03:39:04.463

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

I'm not sure what the original question looked like when you answered this, but given what is there now, it misses the target and is extremely difficult to map to the target. I suspect that is because you had less information to work with then than we do now. — Jonathan Leffler, Oct 22 '08 at 03:15
Yes, the asker changed the question and used a different data set. The answers given above were complete with the dataset given. I have updated the answer to reflect this and added a solution using the new dataset. — vfilby, Oct 22 '08 at 03:39

mrm · Answer 2 · 2008-10-22T03:55:51.310

8

For MySQL:

SELECT contract, activity
FROM table
GROUP BY contract
HAVING COUNT(DISTINCT activity) = 1

edited Oct 22 '08 at 03:55

answered Oct 22 '08 at 02:10

mrm

219
1
4

This also doesn't work, you can't have columns on the select list that are not in the the GROUP BY or an aggregate. You'll have to use the same principle but in a sub query or join. – vfilby Oct 22 '08 at 02:26
1

My bad, this is a limitation of SQL Server. I didn't realize that MySQL could have items on the select list that were not an aggregate function or in the group by. Sorry mate. – vfilby Oct 22 '08 at 03:45

score 3 · Answer 3 · answered Oct 30 '08 at 19:50

3

I'm a fan of NOT EXISTS

SELECT DISTINCT contract, activity FROM table t1
WHERE NOT EXISTS (
  SELECT * FROM table t2
  WHERE t2.contract = t1.contract AND t2.activity != t1.activity
)

answered Oct 30 '08 at 19:50

sliderhouserules

3,415
24
32

Leon Tayson · Answer 4 · 2008-10-22T05:46:30.290

2

Modified!

SELECT distinct contract, activity from @t a
WHERE (SELECT COUNT(DISTINCT activity) FROM @t b WHERE b.contract = a.contract) = 1

And here's another one -- shorter/cleaner without subquery

select contract, max(activity) from @t
group by contract
having count(distinct activity) = 1

edited Oct 22 '08 at 05:46

answered Oct 22 '08 at 02:36

Leon Tayson

4,741
7
37
36

1

When tested in IBM Informix Dynamic Server 11.50, it produces no data. The problem is that contract 2000 has two project rows with the same activity code, so the COUNT(*) is returning 2, not 1. – Jonathan Leffler Oct 22 '08 at 03:13
@Jonathan: the question is about Microsoft SQL Server, so Informix' implementation of SQL is completely irrelevant here. – Milan Babuškov Oct 22 '08 at 05:54
True that IDS is not directly relevant, but it is a valid SQL implementation and the SQL above runs but does not produce the correct answer. – Jonathan Leffler Oct 22 '08 at 16:29

score 2 · Answer 5 · answered Oct 22 '08 at 02:56

2

Try this:

select 
         contract,
        max (activity) 
from
         mytable 
group by
         contract 
having
         count (activity) = 1

answered Oct 22 '08 at 02:56

Walter Mitty

18,205
2
28
58

When tested in IBM Informix Dynamic Server 11.50, this produces no data, not the row with 2000,49 as required. The problem is that contract 2000 has two project rows with the same activity code, so count(activity) = 2 and not 1 as required. – Jonathan Leffler Oct 22 '08 at 03:10
1

This doesn't work on SQL Server 2005. The problem here is that there are two rows for 2000,49. You need to count only distinct activities. – vfilby Oct 22 '08 at 11:42
In the light of these comments, see Jetson's response. – Walter Mitty Jun 14 '11 at 13:47

score 2 · Answer 6 · edited May 05 '15 at 21:43

Utilizing the "dynamic table" capability in SQL Server (querying against a parenthesis-surrounded query), you can return 2000, 49 w/ the following. If your platform doesn't offer an equivalent to the "dynamic table" ANSI-extention, you can always utilize a temp table in two-steps/statement by inserting the results within the "dynamic table" to a temp table, and then performing a subsequent select on the temp table.

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
    [contract],
    [Activity] =  max (activity)
FROM
    (
    SELECT
        [contract],
        [Activity]
    FROM
        @T
    GROUP BY
        [contract],
        [Activity]
    ) t
GROUP BY
    [contract]
HAVING count (*) = 1

score 1 · Answer 7 · answered Oct 22 '08 at 03:06

Assuming your table of data is called ProjectInfo:

SELECT DISTINCT Contract, Activity
    FROM ProjectInfo
    WHERE Contract = (SELECT Contract
                          FROM (SELECT DISTINCT Contract, Activity
                                    FROM ProjectInfo) AS ContractActivities
                          GROUP BY Contract
                          HAVING COUNT(*) = 1);

The innermost query identifies the contracts and the activities. The next level of the query (the middle one) identifies the contracts where there is just one activity. The outermost query then pulls the contract and activity from the ProjectInfo table for the contracts that have a single activity.

Tested using IBM Informix Dynamic Server 11.50 - should work elsewhere too.

score 1 · Answer 8 · answered Oct 22 '08 at 03:20

Here is another option using sql servers count distinct:

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

score 1 · Answer 9 · answered Oct 22 '08 at 03:50

1

SELECT DISTINCT Contract, Activity
FROM Contract WHERE Contract IN (
SELECT Contract 
FROM Contract
GROUP BY Contract
HAVING COUNT( DISTINCT Activity ) = 1 )

answered Oct 22 '08 at 03:50

Hapkido

1,321
1
8
13

score 1 · Answer 10 · answered Oct 30 '08 at 19:20

Sorry you're not using PostgreSQL...

SELECT DISTINCT ON contract, activity * FROM thetable ORDER BY contract, activity

http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-DISTINCT

Oh wait. You only want values with exactly one...

SELECT contract, activity, count() FROM thetable GROUP BY contract, activity HAVING count() = 1

score 0 · Answer 11 · answered Nov 16 '12 at 11:38

0

SELECT DISTINCT Col1,Col2 FROM Table GROUP BY Col1 HAVING COUNT( DISTINCT Col1 ) = 1

answered Nov 16 '12 at 11:38

skygeek

1,548
11
24

score 0 · Answer 12 · answered May 26 '09 at 22:47

Sorry old post I know but I had the same issue, couldn't get any of the above to work for me, however I figured it out.

This worked for me:

SELECT DISTINCT [column]As UniqueValues FROM [db].[dbo].[table]

SQL: How do I SELECT only the rows with a unique value on certain column?

12 Answers12

Linked