How to filter duplicate records in sql

Question

5.00/5 (1 vote)

See more:

SQL

select vCompanyName,vemailid
 from tblmembers

here i dont want duplicate vCompanyName
itried with distinct but not working
pls help..

Posted 6-Oct-11 18:58pm

Rakesh S S

Updated 6-Oct-11 19:01pm

Mehdi Gholam

v2

Add a Solution

Comments

Uday P.Singh 7-Oct-11 1:22am

Are you getting any error?

koolprasad2003 7-Oct-11 2:36am

you can use distinct with either companyname or email_id

Bala Selvanayagam 7-Oct-11 10:13am

vCompanyName vemailid
------------ --------
ABC a@hotmail.com
ABC c@hotmail.com
XYZ b@hotmail.com

I just put some sample data to understand your requirement and please tell me the output you needs based on the above data...company name ABC has two different email address and which one you need and why ?

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

FastEvo8 · Answer 1 · 2011-10-07T04:00:00

Solution 4

You can try this:

SQL

SELECT vCompanyName, MIN(vemailid)
FROM results
GROUP BY vCompanyName

Posted 7-Oct-11 4:00am

FastEvo8

Updated 10-Oct-11 2:15am

v2

anarklai · Answer 2 · 2011-10-10T01:56:00

Solution 5

Try this

SQL

Select tmp.vCompanyName , tblmembers.vemailid
from
(
Select vCompanyName  from tblmembers
group by vCompanyName
having count(vCompanyName) =1
) As tmp
inner join tblmembers on tblmembers.vCompanyName =tmp.vCompanyName

Posted 10-Oct-11 1:56am

anarklai

Comments

Rakesh S S 27-Oct-11 5:46am

anarklai352
great work!!!

P.Salini · Answer 3 · 2011-10-06T19:07:00

Solution 1

Here you are using distinct on entire select query means it will check whether there are any duplicate records in combination of companyname and emailid
thats why you are getting same companyname with different emailid's

if you want unique company names then use this

SQL

select distinct vCompanyName from tblmembers

hope this helps you

Posted 6-Oct-11 19:07pm

P.Salini

Updated 6-Oct-11 19:08pm

v3

Comments

Rakesh S S 7-Oct-11 1:09am

but i want to retrive email id as well ...with same query

P.Salini 7-Oct-11 1:14am

As far as i know it is not possible to get distinct companyname in same query in your case.

Uday P.Singh 7-Oct-11 1:32am

agree my 5

P.Salini 7-Oct-11 1:41am

thanks

Uday P.Singh · Answer 4 · 2011-10-06T19:32:00

Its not possible to get distinct companyname in the same query with emailid.

As this:

SQL

select distinct(vCompanyName),vemailid from results

will result duplicate companyname with email ids.

If you want distinct companyname then use this:

SQL

select distinct(vCompanyName)from results

hope it helps :)

for further queries comment here!

Corporal Agarn · Answer 5 · 2011-10-10T06:56:00

Solution 6

Variation of solution 5

select vCompanyName, MIN(vemailid) as emailid
from tblmembers 
GROUP BY vCompanyName

Notice that this may not give the email address you think it will.

Posted 10-Oct-11 6:56am

Corporal Agarn

How to filter duplicate records in sql

5 solutions

Solution 4

Solution 5

Solution 1

Solution 3

Solution 6

Add your solution here

Preview 0

Existing Members

...or Join us