Geek asks and answers

SQL data types: Grouptext, ntext, and images cannot be compared or sorted

Below is the sql statement and the error I am getting. I want to group all returned items with prodID.

Error:

You cannot compare or sort text, text, and image data types unless you use the NULL or LIKE operator.

Statement:

 SELECT TOP 20 
         PRODID, ITEMDES
         FROM orderedItems oi
         left join orders o on  oi.order_id = o.order_id
    Group by PRODID, ITEMDES
+5
source share
2 answers

No, they cannot. In addition, they are deprecated in favor of types (n)varchar(max).

If you need to group them, change the data structure from (n)textto (n)varchar(max)or do the conversion in the group section

 GROUP BY ProdID, CONVERT(nvarchar(max), ItemDes)
+8
source

ITEMDES , GROUP BY ROW_NUMBER():

select TOP 20 * from 
(

SELECT 
         PRODID,ITEMDES,
         ROW_NUMBER() over (partition by PRODID order by o.order_id) rn
         FROM orderedItems oi
         left join orders o on  oi.order_id = o.order_id
) as t 
where rn=1
order by PRODID; -- any order here you wish
0

More articles:


All Articles