Solved

I got syntax error in my Select Distinct statement

Posted on 2008-10-16
6
367 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
  • 3
  • 2
6 Comments
 
LVL 69

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 69

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 69

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

914 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now