Whats wrong with DISTINCT in this query?

Question

Post reply

Whats wrong with DISTINCT in this query?

gavin.duncan-770062

Ten Centuries

Points: 1066
More actions
June 24, 2008 at 9:48 am

#183969

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?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

steveb.

SSC-Forever

Points: 46733

June 24, 2008 at 9:51 am

#833403

You can't do a group by on a text field.

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 2

Hi, thanks for the reply. Not sure what you mean though........im not using a group by at all.

steveb. SSC-Forever Points: 46733 More actions · Answer 3

steveb.

SSC-Forever

Points: 46733

June 24, 2008 at 9:58 am

#833410

Sorry misread the sql, ignore me 🙂

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 4

lol...........ok, do you have any idea why it is complaining then mate?

Its distinct [ReviewID]'s im looking for. I should also point out that [feManagedReview] is a view.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

When the selected columns include one with a Text or Image datatype, DISTINCT and GROUP BY are not supported.

SQL = Scarcely Qualifies as a Language

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 6

gavin.duncan-770062

Ten Centuries

Points: 1066

June 24, 2008 at 10:04 am

#833418

eh? :ermm:

steveb. SSC-Forever Points: 46733 More actions · Answer 7

apparently you can't do a distinct select on a text field.

try casting them as varchar(max)

select distinct cast(FieldName as varchar(max))

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 8

[ReviewID] is an integer field on the base table which the view is looking at.

steveb. SSC-Forever Points: 46733 More actions · Answer 9

but you are selecting a lot of other fields

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]

I am assuming three of these are text or image fields.

if you only want ReviewID then

SELECT DISTINCT Top 5 [ReviewID]

FROM [feManagedReview]

Only select the one field you need.

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 10

Not sure what the hell is going on in this thread.

The answer is .....

You cannot do a DISTINCT when you are already doing a TOP 5, the two are mutually exclusive

DISTINCT means show me the unique combinations

TOP 5 means show me only the first five from the query

So, you need to decide what you want, if you only want 5 then remove the DISTINCT (and make you you include an order by clause to get the correct 5), if you want all the unique values then remove TOP 5 from your query.

Best of luck.

Nigel West
UK

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 11

Hi Steveb, thanks again.

I need all the fields.........but its only distinct ReviewId's im interested in.

noeld SSC Guru Points: 96590 More actions · Answer 12

nigel.c.west (6/24/2008)
Not sure what the hell is going on in this thread.
The answer is .....
You cannot do a DISTINCT when you are already doing a TOP 5, the two are mutually exclusive
DISTINCT means show me the unique combinations
TOP 5 means show me only the first five from the query
So, you need to decide what you want, if you only want 5 then remove the DISTINCT (and make you you include an order by clause to get the correct 5), if you want all the unique values then remove TOP 5 from your query.
Best of luck.

This is NOT true you can use DISTINCT and TOP in the same query just have to follow the right order of the statement:

SELECT DISTINCT TOP (5) xyz FROM tbl ---valid

SELECT TOP (5) DISTINCT xyz FROM tbl ---

* Noel

steveb. SSC-Forever Points: 46733 More actions · Answer 13

gavin.duncan (6/24/2008)
Hi Steveb, thanks again.
I need all the fields.........but its only distinct ReviewId's im interested in.

are the other fields unique for the ReviewIds?

could you post some sample data?

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 14

Hi Noel

I apologise, I've never had cause to use it before and it really didn't make sense to use both, but having thought about it..............maybe it does have some use.

You live & learn.

Nigel.

Nigel West
UK