Whats wrong with DISTINCT in this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Whats wrong with DISTINCT in this query?

Author

Topic

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-24 : 11:49:06

Hello all.

Ive got the following SQL query and i want to return only distinct [ReviewID]'s, however SQL Server is complaining about it. This is the query:

SELECT DISTINCT Top 5 [ReviewID],[ReviewType],[ReviewTypeName],[LoginID],[LoginForename],[LoginSurname],[Approved],[ReviewDate]
			   ,[Stars],[RelatedProductID],[Title],[Copy],[Rating], [Image1], [Image1Width], [Image1Height], [Image1AltText], [Image2], [Image2Width], [Image2Height], [Image2AltText],
				[CategoryL4]
		FROM [feManagedReview]

This is whats its complaining about:

Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.

Any ideas where im going wrong?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-24 : 12:05:05

It means just what it says. It can't compare a text field in record 1 to the same field in another record. Try converting your text fields to varchar(max).

Jim

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-24 : 12:13:39

I dont understand that. ReviewId is an integer field.

Should point out that [feManagedReview] is a view.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-06-24 : 12:18:39

you must have other fields in yoru selection that are text field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 01:30:54

quote:
Originally posted by Gavin1977

I dont understand that. ReviewId is an integer field.

Should point out that [feManagedReview] is a view.

i think one of [Image1AltText],[Image2AltText] is of text datatype which causes this. you could try casting it to varchar after taking len of longest string data from field and then take distinct.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources