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 2012 Forums
 Transact-SQL (2012)
 Distinct and Group by is not working

Author  Topic 

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-28 : 13:15:12
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-28 : 13:41:23
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-28 : 13:45:41
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
Go to Top of Page
   

- Advertisement -