DISTINCT w/ character data

22 Sep 2005 2:21 PM

alto

Hello,

I need to eliminate duplicates from records containing a text data type.
Here is the query I try :

---
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
---

And here is the error I get :

---
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
---

The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?

TIA

22 Sep 2005 2:43 PM

David Portas

DISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):

SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;

--
David Portas
SQL Server MVP
--

22 Sep 2005 2:45 PM

mike

The keyword DISTINCT needs to be before any field names. Also, CAST should be
outside of the parentheses. Try the following

SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName .....

Show quote

"alto" wrote:

> Hello,
>
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
>
> ---
> select NewsGroup.NewsGroupID,
>    (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
>   -- Line 10
>    NewsGroup.OnlineFlag
> from...
> where...
> ---
>
> And here is the error I get :
>
> ---
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> ---
>
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
>
> TIA
>
>
>