Whats wrong with DISTINCT in this query?

  • 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?

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

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

  • Sorry misread the sql, ignore me 🙂

  • 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.

  • 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

  • eh? :ermm:

  • 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))

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

  • 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.

  • 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

  • Hi Steveb, thanks again.

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

  • 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

  • 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?

  • 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

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

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