Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

This was last published in March 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close