Using DISTINCT with TEXT, NTEXT or IMAGE
I get the following 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 remove the duplicate rows?
I get the following message:
Server: Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.
And I'm running the following query:
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
How do I remove the duplicate rows?
The simple answer is: you cannot, not like that. That error message is pretty explicit about what's wrong with your query. Apparently you've included at least one TEXT, NTEXT or IMAGE column in your SELECT. You cannot do that and use DISTINCT at the same time.
A closer look at your query suggests that you probably aren't getting true duplicates. You have not mentioned anything about the cardinality of your relationships. Does each requirement have multiple tests, or does a test have multiple requirements? What about test definitions, are they one-to-many with tests or with requirements? Or vice versa? It is next to impossible to tell just by looking at the query. Nevertheless, if the data is correctly joined, you should not be getting true duplicate rows.
What could be happening is that a single test or requirement or whatever is appearing in your result set multiple times, but this is different from entire rows being duplicated.
Some people get query results like this:
aaa 101 aaa 102 aaa 103 bbb 201 bbb 204
Then they want to "remove" the "duplicates" so that the results look like this:
aaa 101
102
103
bbb 201
204
However, those weren't true duplicate rows, and you should not attempt to suppress the repeating values with SQL. Handle this in your front-end application.
If this isn't an accurate understanding of your situation, then I'm sorry, I guess there wasn't enough information provided with your question.
