Eliminating Duplicates in Select

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)

Eliminating Duplicates in Select

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-04-11 : 19:04:51

Hi All,

I need to eliminate Duplicates in my Sql Query, tried to use distinct and that doesn't seem to work, can anybody pls.help.

duplicates are in #ddtempC table, and am writing a query to get a country name from the hash table where hash table has duplicates

hash table contains (THEATER_CODE, COUNTRY_CODE, COUNTRY_NAME).
and trying to write condition on THEATER_CODE and COUNTRY_CODE to get Country_name

and THEATER_CODE AND COUNTRY_CODE HAS DUPLICATES. whenever i do a sub query i get the below error.

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT USER_FIRSTNAME, USER_LASTNAME,
user_countryCode,
USER_COUNTRY = (SELECT DISTINCT RTRIM(LTRIM(COUNTRY_NAME)) FROM #ddtempC WHERE RTRIM(LTRIM(COUNTRY_CODE)) = USER_COUNTRYCODE AND RTRIM(LTRIM(THEATER_CODE)) = USER_THEATERCODE)
FROM [user]
WHERE USER_USERNAME IS NOT NULL AND User_CreationDate BETWEEN '1/2/2007' AND '4/11/2008'
ORDER BY User_TheaterCode;

Thanks in Advance.

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-04-11 : 21:28:14

Ill take a guess... lets see what this returns. Youll probably find that you are not getting a single value for t.USER_COUNTRY


SELECT	u.USER_FIRSTNAME,
	u.USER_LASTNAME,
	u.user_countryCode,
	t.USER_COUNTRY
FROM	[user] u
inner
join	#ddtempc t on
	u.User_CountryCode = t.Country_Code and
	u.User_Theater_Code = t.User_Theater_Code
WHERE	U.USER_USERNAME IS NOT NULL AND 
	U.User_CreationDate BETWEEN '1/2/2007' AND '4/11/2008'
ORDER 
BY	User_TheaterCode;

It will help if you post ddl creates, sample data, and the desired results for us.

Nathan Skerl

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-04-12 : 01:11:19

You're error is almost definantly due to you have two differently spelled country names with the same theatercode and country code.

To resolve use a rownumber query.



SELECT	
	 u.USER_FIRSTNAME
	,u.USER_LASTNAME
	,u.USER_COUNTRYCODE
	,a.COUNTRY_NAME
FROM	
	[user] u
inner join	
	(	
		Select 
			 Row_Number() over (Partition By aa.COUNTRY_CODE,aa.THEATERCODE order by aa.Country_Name) as RowID
			,Ltrim(rtrim(aa.Country_Name)) as COUNTRY_NAME
			,Ltrim(rtrim(aa.Country_Code)) as COUNTRY_CODE
			,Ltrim(rtrim(aa.Theater_Code)) as THEATER_CODE
		From
			#ddtempc aa
	) a
on
	ltrim(rtrim(u.USER_COUNTRYCODE)) = a.Country_Code and
	ltrim(rtrim(u.USER_THEATERCode)) = a.THEATER_CODE
WHERE	
	U.USER_USERNAME IS NOT NULL 
AND	U.USER_CREATIONDATE BETWEEN '1/2/2007' AND '4/11/2008'
and     a.ROWID = 1
ORDER 
BY	u.USER_THEATERCODE

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-04-14 : 20:31:36

Hi Vinnie,

It worked, and yes what you said is correct about two differently spelled country names with the same theatercode and country code,

i don't think am able to write this without ur help.

thanks a lot bro.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources