Distinct cols of more than one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Distinct cols of more than one

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2005-08-30 : 07:19:22

Hi

There is a table which has repeated values in more than one col.
I want to view all the distinct values for both the cols

for eg

col1 col2 col3
A B D
A B E
B C E
B C F

I want the output as

col1 col2
A B
B C

cheers
Vic

Vicky

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-30 : 07:30:35

Try this

Select col1, col2 from yourTable group by col1, col2 having count(*)>1

Madhivanan

Failing to plan is Planning to fail

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-30 : 15:52:05

Interestingly enough when you include the 3rd column you get NOTHING:

DROP TABLE [Fowler].[dbo].[Characters_IN]

CREATE TABLE [Fowler].[dbo].[Characters_IN]
(Col_1 varchar(1) NULL, Col_2 varchar (1) NULL, Col_3 varchar (1) NULL)
GO
INSERT Characters_IN VALUES('A','B','D')
INSERT Characters_IN VALUES('A','B','E')
INSERT Characters_IN VALUES('B','C','E')
INSERT Characters_IN VALUES('B','C','F')
GO

SELECT * FROM [Fowler].[dbo].[Characters_IN] GO

SELECT Col_1, Col_2, Col_3 FROM [Fowler].[dbo].[Characters_IN] GROUP BY Col_1, Col_2, Col_3 HAVING COUNT(*)>1
GO

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 00:52:39

>>Interestingly enough when you include the 3rd column you get NOTHING:

Thats not the question from questioner

See what you get here

DROP TABLE [Fowler].[dbo].[Characters_IN]

CREATE TABLE [Fowler].[dbo].[Characters_IN]
(Col_1 varchar(1) NULL, Col_2 varchar (1) NULL, Col_3 varchar (1) NULL)
GO
INSERT Characters_IN VALUES('A','B','D')
INSERT Characters_IN VALUES('A','B','E')
INSERT Characters_IN VALUES('B','C','E')
INSERT Characters_IN VALUES('B','C','F')
INSERT Characters_IN VALUES('B','C','F')
GO

SELECT * FROM [Fowler].[dbo].[Characters_IN] GO

SELECT Col_1, Col_2, Col_3 FROM [Fowler].[dbo].[Characters_IN] GROUP BY Col_1, Col_2, Col_3 HAVING COUNT(*)>1
GO

Madhivanan

Failing to plan is Planning to fail

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-31 : 14:21:43

OK, you're right...it wasn't the question from the questioner, but I was curious nonetheless

After running your suggestion....here's what I got:

Col_1 Col_2 Col_3
----- ----- -----
A B D
A B E
B C E
B C F
B C F

(5 row(s) affected)

Col_1 Col_2 Col_3
----- ----- -----
B C F

(1 row(s) affected)

Only one row---the very last one, too. So...what point were to trying to make with this, Madhi?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 00:33:33

The thing is to find the combination of columns that occur more than once
Here B C F occur more than once (three times) so is answer

Madhivanan

Failing to plan is Planning to fail

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-01 : 11:24:54

Thanks, Madhi...

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

sreeram84n
Starting Member

4 Posts

Posted - 2012-06-08 : 02:10:02

select distinct col_1,col_2 from Characters_IN

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-08 : 02:31:57

You can do it like this as well:


--Creating Tables

Create Table Ex
(col1 char(1),
 col2 char(1),
 col3 char(1) )


--Inserting Data

Insert into Ex
Select 'A', 'B', 'D'
Union ALL
Select 'A', 'B', 'E'
Union ALL
Select 'B', 'C', 'E'
Union ALL
Select 'B', 'C', 'F'


--Query For Your Requirement

Select Col1, col2 From
(Select *, ROW_NUMBER() Over (Partition By Col1, Col2 Order By (Select NULL)) As rn From Ex) As a
Where rn = 1

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources