Message 8163 – Trying to remove duplicate rows.

I am trying to remove duplicate rows with the following SQL: SELECT DISTINCT r.ReqName AS "Requirement Name", r.ReqDescription AS "Requirement Description", r.ReqID AS "Requirement ID", r.ReqReviewed AS "Requirement Reviewed", r.ReqCoverageStatus AS "Requirement Coverage Status", t.TestName AS "Test Name", t.TestDescription AS "Test Decsription", t.TestID AS "Test ID", t.TestContainerName AS "Test Container Name", r.ProjectName AS "Project Name", t.TestCreator AS "Test Creator", t.TestCreated AS "Test Created", d.CurrentExecStatus AS "Current Execution Status", t.TestTypeName AS "Test Type" FROM RTM_V_Requirements r, RTM_V_ReqTests t, RTM_V_TestDefinitions d WHERE r.ProjectName = 'New Hampshire Testing Project' AND r.ProjectName = t.ProjectName AND r.ProjectName = d.ProjectName But I get this message: Server: Msg 8163, Level 16, State 3, Line 1 The text, ntext, or image data type cannot be selected as DISTINCT. How do I resolve this? Thanx for your help. Steve

Answer Wiki

Thanks. We'll let you know when a new response is added.

SQL Server 6.5 no longer allows a ‘SELECT DISTINCT * FROM table’ if that table has a text/image column. If you attempting to perform such a query, you will receive the following error message:

Msg 8163: Text/Image datatype cannot be selected as DISTINCT.

Earlier versions of SQL Server treated this as a valid query if the table contained a unique index or primary key, whose existence would guarantee that the row was distinct. In the presence of such an index, the following message would have been raised:

Msg 421: TEXT and IMAGE datatypes may not be selected as DISTINCT.
SQL Server has never allowed a DISTINCT operation on an individual text
or image column.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Pphillips001
    Hi, Firstly you only need the IDs to perform the delete. Secondly why don't you create a subquery to count iterations and only pull in ID's with more than one occurance? SELECT r.ReqID FROM RTM_V_Requirements r WHERE (Select count(r2.ReqID) FROM RTM_V_Requirements r2 WHERE r.ReqName = r2.ReqName) > 1 GROUP BY r.ReqID or some such derivative. It's hard to give an exact result as I don't know which table(s) you're deleting from and have no data to work with. Hope this helps. Paul
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: