Solved

I got syntax error in my Select Distinct statement

Posted on 2008-10-16
6
370 Views
Last Modified: 2008-10-16
Hi, I'm selecting a bunch of fields from a few tables in a single select statement.  Here I want to select from table A with unique email adrress.  However, I'm getting a syntax error.  How can I resolve this error?
Also, I wan to make sure I select the record in table A where the id column has the lowest number in the event that there is duplicate records with the same email address.
SELECT [id], DISTINCT[email], [col1], [col2], [col3]
from TableA
0
Comment
Question by:lapucca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 22735162
DISTINCT can only apply to the entire row:

SELECT DISTINCT [id], [email], [col1], [col2], [col3]
from TableA
0
 

Author Comment

by:lapucca
ID: 22735219
1.  Can you suggest a way for me to get records from TableA with no duplicate email?
2.  I worked with Query Analyzer a few years back with SQL 2000.  How do I create Stored procedure in sql2005 since I can't seem to find Query Analyzer with this version.

Thanks.
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 22735288
1.
SELECT A.[id], A.[email], A.[col1], A.[col2], A.[col3]
from TableA AS A
inner join (SELECT min([id]) AS ID, [email] from TableA group by email) AS B
ON B.ID = A.ID

2. QA has been replaced by SSMS (SQL Server Management Studio)
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 1

Expert Comment

by:homecoder
ID: 22735324
I am unsure of exactly what you are trying to do, but here are a couple of queries to try.. I hope this helps a little..


-- to Select all
SELECT DISTINCT [id], [email], [col1], [col2], [col3] FROM table_a WHERE [email] = 'email@to.check' ORDER BY id ASC
 
-- SELECT Only 1 record (the first one)
SELECT DISTINCT TOP 1 [id], [email], [col1], [col2], [col3] FROM table_a WHERE [email] = 'email@to.check' ORDER BY id ASC
 
-- to SELECT all with a unique email address (having one 1 email record)
SELECT [id], [email], [col1], [col2], [col3] 
FROM table_a
WHERE (email IN (SELECT DISTINCT email FROM table_a GROUP BY email HAVING count(email) = 1))
ORDER BY id ASC

Open in new window

0
 

Author Comment

by:lapucca
ID: 22735398
Hi emoreau,
The id field in TableA is the identity and the primary key field in this table.  Would the query you provide remains the same?  Thank you.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 22736586
>>The id field in TableA is the identity and the primary key field in this table

It has no impact on the query.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question