Use DISTINCT with two columns together

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

SQL Server 2005 Forums

Transact-SQL (2005)

Use DISTINCT with two columns together

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-02-23 : 09:52:41

I have two sets of string values that are being passed into a proc, then I am joining them together into a table variable.
Within the table variable, I need to remove all duplicate rows (e.g. where colA and colB have the same data on multiple rows).

Can anyone advise how to do this please? I've seen dozens of methods on Google but all seem to come under fire from the pro's for being highly inefficient.


DECLARE @tbl_u TABLE (ID int IDENTITY(1,1), colA int)
DECLARE @tbl_s TABLE (ID int IDENTITY(1,1), colB int)

INSERT INTO @tbl_u (colA) SELECT IdVal FROM dbo.fn_CreateIdTable(N'3,4,5,41,3,43,44,46,52,55,56,51,3,4,5,41,43,44,46,52,55,56,51,', ',')
INSERT INTO @tbl_s (colB) SELECT IdVal FROM dbo.fn_CreateIdTable(N'14,14,14,14,14,14,14,14,14,14,14,14,9,9,9,9,9,9,9,9,9,9,9,', ',')

SELECT
	u.ID,
	u.colA,
	s.colB
FROM
	@tbl_s s INNER JOIN
	@tbl_u u ON u.ID = s.ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 09:56:40

[code]
SELECT DISTINCT
u.colA,
s.colB
FROM
@tbl_s s INNER JOIN
@tbl_u u ON u.ID = s.ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 09:58:00

if you want ID also to be retrieved you can get only one of associated value when you take distinct. so you need to apply some kind of aggregation like MIN(),MAX() etc over it


SELECT MIN(u.ID)
	u.colA,
	s.colB
FROM
	@tbl_s s INNER JOIN
	@tbl_u u ON u.ID = s.ID
GROUP BY u.colA,
	s.colB

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-02-23 : 10:06:14

Ahh okay; I understand.

Wicked, thanks visakh16!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 10:08:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources