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.

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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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.

This was first published in March 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top