sql - remove / ignore duplicates from result

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

sql - remove / ignore duplicates from result

Author

Topic

dwair
Starting Member

4 Posts

Posted - 2009-02-10 : 06:29:40

Hi

I am new to working with database's and need some help figuring out what is probably a very simple question...

I have a problem with finding / removing duplicates in the results for an MS-SQL table and displaying just one version of the duplicated row. I do not want to delete and duplicated records.

The table contains various rows which have duplicated fields (email and address etc) but a unique id for each row.

What I want to do is pull out a single row of data for each email address, but not have any duplicate email or addresses in the list

I have managed to get as far as listing all the email addresses with no duplicates but can’t get round the GROUP BY as soon as I add another field, the results display everything including the duplicates I want rid of.

What I have so far:
SELECT TOP 100 PERCENT Enquiry.email
FROM Enquiry
GROUP BY Enquiry.email
HAVING COUNT(Enquiry.email) > 0

This gives me a list with no duplicated email address but how do I obtain the rest of the fields for each row that this selects?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 06:31:51

can you post some sample data and explain what according to you are duplicates?

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-10 : 06:59:46

[code]
select t.* from
(select *,row_number() over(partition by email order by col) as rowno from enquiry) t
where t.rowno = 1

[/code]

Jai Krishna

dwair
Starting Member

4 Posts

Posted - 2009-02-10 : 12:48:49

Jai - don't know what I'm doing wrong but I get:
"Error in list of arguments: 'OVER' not recognised
Missing FROM clause"
when I try your select

Visakh - Sorry I should have posted a data sample and what I hoped to achieve earlier as my question would probably made more sense.

Table / data
id: type: address: email:
1 1 address1 email1
2 2 address1 email1
3 3 address1 email1
4 4 address2 email2
5 5 address2 email2
6 6 address2 email2

Required result
address: email:
address1 email1
address2 email2

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 13:18:25

Can't you use simply? Maybe wrong?

Select distinct address,email from table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 22:50:01

if your posted output is what you want sodeeps suggestion should work fine

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-11 : 00:30:11

quote:
Originally posted by dwair

Jai - don't know what I'm doing wrong but I get:
"Error in list of arguments: 'OVER' not recognised
Missing FROM clause"
when I try your select

r u working with sql2000 r 2005
In 2000 row_number will not works it works from 2005 & above

dwair
Starting Member

4 Posts

Posted - 2009-02-11 : 04:36:20

Ahh, will try Jai's suggestion on the 2005 version and will see what happens. I have to develop on a test db that is sql2k then work with a live 2005 db. Don't ask me why the company has done this as it drives me mad!!

dwair
Starting Member

4 Posts

Posted - 2009-02-11 : 04:46:53

It Works!!

Many thanks for all your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 08:37:45

quote:
Originally posted by dwair

Ahh, will try Jai's suggestion on the 2005 version and will see what happens. I have to develop on a test db that is sql2k then work with a live 2005 db. Don't ask me why the company has done this as it drives me mad!!

you can do a whole lot of things much easier in sql 2005 than in sql 2k thanks to new t-sql features.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources