How to write disctict along with primary key in sqlserver 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

SQL

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 22-Sep-11 22:32pm

Anil Honey 206

Updated 22-Sep-11 22:50pm

André Kraak

v2

Add a Solution

3 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)

himanshu034 · Answer 1 · 2011-09-22T22:54:00

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

Posted 22-Sep-11 22:54pm

himanshu034

Updated 7-Oct-11 20:57pm

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

OriginalGriff · Answer 2 · 2011-09-22T22:41:00

Solution 1

Remove the brackets:

SQL

SELECT DISTINCT Emailid FROM StoreUserEmails

See the definition of distinct[^]

[edit]Removed spurious pre tag - OriginalGriff

Posted 22-Sep-11 22:41pm

OriginalGriff

Updated 22-Sep-11 22:42pm

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.

Ganu Sharma · Answer 3 · 2011-10-07T20:53:00

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

Posted 7-Oct-11 20:53pm

Ganu Sharma

Updated 7-Oct-11 20:58pm

v2