Using distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using distinct

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-21 : 11:28:59

The text data type cannot be selected as DISTINCT because it is not comparable.

SELECT
distinct A.ArticleID,A.ArticleGUID,A.Title,A.ArticleContent,A.WrittenByUserID,A.ApprovedByUserID,
A.ActiveDate,A.TerminationDate,A.SortOrder,A.SecurityLevel,A.DateAdded,A.DateUpdated,A.UserAdded,A.UserUpdated,B.GroupID
INTO
#ArticleGroupInfo
FROM
#Articles A left outer join
ArticleGroupLink B
ON
A.ArticleID=B.ArticleID;

How can I fix this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 11:32:12

If you're using sql 2005, try converting text field to varchar(max). If sql 2000 or earlier and if you're sure that length of data in field falls within 800 characters, try casting to varchar(8000)

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-10-21 : 11:58:08

error is gone.

But my query logic is wrong. I need to get a result from two tables.I have an Article Table and a Group table . In this case Article can belong to many groups but I need it in the result only once.
Article Table
ArticleID,Contentetc
Group Table
GroupID,ArticleID etc
I need in the result ArticleID,Content,GroupID
ArticleID must be unique how can I do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 12:01:31

quote:
Originally posted by BendJoe

error is gone.

But my query logic is wrong. I need to get a result from two tables.I have an Article Table and a Group table . In this case Article can belong to many groups but I need it in the result only once.
Article Table
ArticleID,Contentetc
Group Table
GroupID,ArticleID etc
I need in the result ArticleID,Content,GroupID
ArticleID must be unique how can I do this?

SELECT a.ArticleID,a.Content,g.GroupID
FROM Article a
INNER JOIN Group g
ON g.ArticleID=a.ArticleID
INNER JOIN (SELECT ArticleID,MAX(GroupID) AS MaxGroup
            FROM Group
            GROUP BY ArticleID)g1
ON g1.ArticleID=g.ArticleID
AND g1.MaxGroup=g.GroupID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources