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
Solved

I got syntax error in my Select Distinct statement

Posted on 2008-10-16
6
369 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 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
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…

828 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