Click here to Skip to main content
14,635,827 members
Rate this:
Please Sign up or sign in to vote.
Dear Friends,

Here i have same emailid in my table so i want to get only one email id

ex:1,anil@gmail.com
   2,anil@gmail.com


so i want to fetch only one email id from database and bind to gridview.

so i have written a query like this

select distinct(Emailid),StoreUserId from StoreUserEmails


but its not coming

if i use like this select distinct(Emailid) from StoreUserEmails

the error is coming like this when i bind to gridview
Error:Keyfeildname is essential


Please solve my problem

Regards,

Anilkumar.D
Posted
Updated 22-Sep-11 22:50pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

SELECT  *
FROM    (SELECT EmailId, StoreUserId,
               ROW_NUMBER() OVER (PARTITION BY EmailId ORDER BY StoreUserId) AS RowNumber
         FROM   StoreUserEmails
         WHERE  EmailId LIKE 'anil@gmail.com') AS a
WHERE   a.RowNumber = 1




hope this will help you......
   
v4
Comments
Anil Honey 206 23-Sep-11 5:15am
   
thanks its working but i have written this query

SELECT DISTINCT Emailid,
(SELECT TOP (1) StoreUserId
FROM StoreUserEmails AS StoreUserEmails1
WHERE (Emailid = StoreUserEmails2.Emailid)) AS StoreUserId
FROM StoreUserEmails AS StoreUserEmails2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Remove the brackets:
SELECT DISTINCT Emailid FROM StoreUserEmails


See the definition of distinct[^]

[edit]Removed spurious pre tag - OriginalGriff
   
v2
Comments
Anil Honey 206 23-Sep-11 4:47am
   
But i want along with primary key id

ex:1 anil@gmail.com
2 anil@gmail.com if execute any query the result should come

1 anil@gmail.com

for this i want query along with id.
Rate this:
Please Sign up or sign in to vote.

Solution 3

try this.

begin

select  * into #val from val
create table #output(id int, email varchar(100))


while((select COUNT(*) from #val) > 0)
begin

 
	insert #output   select top(1) * from #val
    delete from #val where email =    (select top(1) email from #val)
    
end
select * from #output
end
   
v2

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




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