Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

I got syntax error in my Select Distinct statement

Posted on 2008-10-16
6
Medium Priority
?
379 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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