DISTINCT w/ character data

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?
TIADISTINCT 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
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"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
>
>sql

display FLOAT datatype with commas?

Is it possible to display a FLOAT datatype with all its commas every
three digits? I can't figure out how to use CAST or CONVERT to do this.
(I know this kind of thing is usually done on the client side, but
that's not possible right now.) Thanks much.here you go
DECLARE @.v float
SELECT @.v = 1322323.6666
SELECT CONVERT(VARCHAR,convert(money,@.v),1)
http://sqlservercode.blogspot.com/|||Why is this a float?
Anyway, see http://tinyurl.com/zlbmw
This is not pretty.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1e7917a698fb998c989921@.msnews.microsoft.com...
> Is it possible to display a FLOAT datatype with all its commas every
> three digits? I can't figure out how to use CAST or CONVERT to do this.
> (I know this kind of thing is usually done on the client side, but
> that's not possible right now.) Thanks much.|||FANTASTIC. Thank you.
Is there a way to right-justify the output? (I know we have character
data now, so it may not be possible?) Thanks.
In article <1141853921.455400.117020@.j52g2000cwj.googlegroups.com>,
denis.gobo@.gmail.com says...
> here you go
> DECLARE @.v float
> SELECT @.v = 1322323.6666
> SELECT CONVERT(VARCHAR,convert(money,@.v),1)
> http://sqlservercode.blogspot.com/
>|||What if it's a bigger FLOAT value than MONEY can support?
DECLARE @.v float
SELECT @.v = 1321324441322323.6666
Also, you should always specify the size of your VARCHAR parameters, IMHO.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141853921.455400.117020@.j52g2000cwj.googlegroups.com...
> here you go
> DECLARE @.v float
> SELECT @.v = 1322323.6666
> SELECT CONVERT(VARCHAR,convert(money,@.v),1)
> http://sqlservercode.blogspot.com/
>|||> Is there a way to right-justify the output?
Umm, sure, you could say RIGHT(SPACE(30)+...stuff..., 30)
But what application is consuming this result that can't right-justify ?
Are you going to ask for a way to embed <img> tags next? :-)
T-SQL is not meant to be a presentation language.|||What is the basic principle of a tiered architecture? We do display
formatting in the front end and never in the database. Since FLOAT
can come back as exponential notation, you really don't want to do it
in SQL.|||Brilliant. Thank you VERY much. Much appreciated. I know T-SQL is not
meant to be a presentation language, but for a very quick one-time
temporary project I am forced to use it as such. This is perfect.
Thank you.
In article <OPUigrvQGHA.4956@.TK2MSFTNGP09.phx.gbl>,
ten.xoc@.dnartreb.noraa says...
> Umm, sure, you could say RIGHT(SPACE(30)+...stuff..., 30)
> But what application is consuming this result that can't right-justify ?
> Are you going to ask for a way to embed <img> tags next? :-)
> T-SQL is not meant to be a presentation language.
>
>