Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
select vCompanyName,vemailid
 from tblmembers 
here i dont want duplicate vCompanyName
itried with distinct but not working
pls help..
Posted 6-Oct-11 19:58pm
Edited 6-Oct-11 20:01pm
Mehdi Gholam253.2K
v2
Comments
Uday P.Singh at 7-Oct-11 1:22am
   
Are you getting any error?
koolprasad2003 at 7-Oct-11 2:36am
   
you can use distinct with either companyname or email_id
Bala Selvanayagam at 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 ?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

You can try this:
 
SELECT vCompanyName, MIN(vemailid)
FROM results
GROUP BY vCompanyName
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Try this
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
  Permalink  
Comments
Amol_Joshi at 27-Oct-11 5:46am
   
anarklai352
great work!!!
Rate this: bad
good
Please Sign up or sign in to vote.

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
 
select distinct vCompanyName from tblmembers
 
hope this helps you
  Permalink  
v3
Comments
Amol_Joshi at 7-Oct-11 1:09am
   
but i want to retrive email id as well ...with same query
CodeHelper_PS at 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 at 7-Oct-11 1:32am
   
agree my 5
CodeHelper_PS at 7-Oct-11 1:41am
   
thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Its not possible to get distinct companyname in the same query with emailid.
 
As this:
 
select distinct(vCompanyName),vemailid from results
 
will result duplicate companyname with email ids.
 
If you want distinct companyname then use this:
 
select distinct(vCompanyName)from results
 

hope it helps Smile | :)
 
for further queries comment here!
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 5,170
1 DamithSL 4,357
2 Maciej Los 3,750
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,851


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 10 Oct 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100