SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Distinct and Group by is not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 03/28/2013 :  13:15:12  Show Profile  Reply with Quote
Good afternoon, I have a simple query that I thought was designed to get a "distinct" set of records based on a combined field.

Here is the code:

use slx_test
go

select DISTINCT ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(lastname)) as displayname,
ltrim(rtrim(email)),ltrim(rtrim(title)),'0'
from sysdba.contact

where [lastName] is not null and 'displayname' is not null and
DataLength(Ltrim(Rtrim('displayname'))) > 5 and
[email] is not null and [title] is not null

GROUP BY ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(lastname)),email,title
ORDER BY displayname

Here is the output

Abraham Hidary AHIDARY@456.COM President
Abraham Merchant xxcdd@merchantshospitality.com CEO
Abraham Merchant xxcfde@merchantshospitality.com Owner
Abraham Minto abraham.123@ge.com Technical Advisory Manager

The 2nd and 3rd rows have the same "displayname"

Thanks



Bryan Holmstrom

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 03/28/2013 :  13:41:23  Show Profile  Reply with Quote
The rows are distinct. Which of those two "duplicate" rows do you want?

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 03/28/2013 :  13:45:41  Show Profile  Reply with Quote
Here is a sample that might help get you going (untested due to no sample data):
SELECT
	*
FROM
	(
		select 
			ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(lastname)) as displayname,
			ltrim(rtrim(email)) AS Email,
			ltrim(rtrim(title)) AS Title,
			'0' AS Unknown,
			ROW_NUMBER() OVER 
			( 
				PARTITION BY ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(lastname)) 
				ORDER BY ??
			) AS RowNum
		from 
			sysdba.contact
		where 
			[lastName] is not null 
			and displayname is not null 
			and DataLength(Ltrim(Rtrim(displayname))) > 5 
			and [email] is not null 
			and [title] is not null 
		GROUP BY 
			ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(lastname)),
			email,
			title
	) AS A
WHERE 
	ROwNum = 1

Edited by - Lamprey on 03/28/2013 13:46:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000