CODEWITHSUNDEEP
sqlsql-server
GMR
GMR

Reputation: 111

MSSQL 2000 database error while using GROUP BY clause

When trying to run the following query to pull the first instance of the number column:

SELECT number, comment
FROM workitem
WHERE (NOT (status_lookup_id IN (400, 600))) 
  AND (modified_on < DATEADD(dd, - 5, GETDATE())) 
  AND (due_on < GETDATE())
GROUP BY number, comment

I get the following error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I understand why the error is being thrown, I think, but I would need that information from the comments column. Is there some other way to get this data?

Upvotes: 0

Views: 108

Answers (3)

Mukesh Chudasama
Mukesh Chudasama

Reputation: 180

you can cast & then use like below:

SELECT     cast(number as varchar(max)), cast(comment as varchar(max))
FROM         workitem
WHERE     (NOT (status_lookup_id IN (400, 600))) AND (modified_on < 
DATEADD(dd, - 5, GETDATE())) AND (due_on < GETDATE())
GROUP BY cast(number as varchar(max)), cast(comment as varchar(max))

It will truncate comment & number if its more then allowed char

Upvotes: 2

Vikrant
Vikrant

Reputation: 5036

It's not because of Group By, but the datatype of the column you are trying to Group by.

SELECT number, comment
FROM workitem
WHERE (NOT (status_lookup_id IN (400, 600))) 
    AND (modified_on < DATEADD(dd,-5, GETDATE())) AND (due_on < GETDATE())
GROUP BY cast(number as varchar(max)), cast(comment as varchar(max))

Upvotes: 1

Allan Brejnholt B&#248;ge Velser
Allan Brejnholt B&#248;ge Velser

Reputation: 1

After some googling, it seems to be your data types that are wrong? You didn't post them so I'm just assuming.

Try using this fix -> The text, ntext, and image data > types cannot be compared or sorted, except when using IS NULL or LIKE > operator

Upvotes: 0

Related Questions