?
Solved

I got syntax error in my Select Distinct statement

Posted on 2008-10-16
6
Medium Priority
?
374 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 2000 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
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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