DISTINCT problem with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DISTINCT problem with query

Author

Topic

dolot
Starting Member

8 Posts

Posted - 2010-08-02 : 19:42:56

I have the following query:

SELECT * FROM FACILITY_INFO WHERE FACILITY_OID = 6598 
UNION  
SELECT * FROM FACILITY_INFO WHERE FACILITY_OID IN 
(SELECT FACILITY_A FROM FACILITY_BARRICADING WHERE FACILITY_B = 6598)  
UNION  
SELECT * FROM FACILITY_INFO WHERE FACILITY_OID IN 
(SELECT FACILITY_B FROM FACILITY_BARRICADING WHERE FACILITY_A = 6598)

I'm getting the error: The text data type cannot be selected as DISTINCT because it is not comparable.

Since I'm not using the DISTINCT keyword, I suspect this is a red herring. Does anybody know what may be causing this error?

konark
Yak Posting Veteran

60 Posts

Posted - 2010-08-02 : 20:21:20

Well try this ..

create table dbo.test1
( col1 varchar(1000) , col2 text )
insert into dbo.test1 values ( 'ABCD', 'ABCD1' )

create table dbo.test2
( col1 varchar(1000) , col2 text )
insert into dbo.test2 values ( 'ABCD', 'ABCD1' )
select * from dbo.test1
union
select * from dbo.test2

Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.

replace union with union all .It works.

Point to be noted that UNION does a distinct and sort .That is why you got the mesage.
'Union all' does not perform distinct and sort .

Also please do not use union on select *. Use specific columns.

Chandragupta Mourya

Chandragupta Mourya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 01:59:57

also if you have control, better to use varchar(max) instead of text if you're on sql 2005 or above. datatypes like text,ntext etc are deprecated from sql 2005 onwards.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dolot
Starting Member

8 Posts

Posted - 2010-08-03 : 09:44:00

Thanks, everyone.

If I understand correctly, I can't use DISTINCT on a Text datatype column, and UNION performs an implicit DISTINCT.

So... if I need the data to be DISTINCT I'll have to change any text datatypes to VARCHAR(MAX).

But the problem may go away if I don't do a SELECT *, but rather select just the columns I need.

Let me know if I've interpreted anything incorrectly.

Thanks again,
Dave

xpandre
Posting Yak Master

212 Posts

Posted - 2010-08-03 : 10:25:44

you can try casting..

SELECT FACILITY_OID, cast(acolumnthatistexttype as varchar(max)) FROM FACILITY_INFO WHERE FACILITY_OID = 6598
UNION
SELECT FACILITY_OID, cast(acolumnthatistexttype as varchar(max)) FROM FACILITY_INFO WHERE FACILITY_OID IN
(SELECT FACILITY_A FROM FACILITY_BARRICADING WHERE FACILITY_B = 6598)
UNION
SELECT FACILITY_OID, cast(acolumnthatistexttype as varchar(max)) FROM FACILITY_INFO WHERE FACILITY_OID IN
(SELECT FACILITY_B FROM FACILITY_BARRICADING WHERE FACILITY_A = 6598)

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-03 : 11:12:54

quote:
Originally posted by dolot
But the problem may go away if I don't do a SELECT *, but rather select just the columns I need.

And the crowd is on their feet here Johnny.....

Best Practices

ALWAYS only Select the columns you need
NEVER use SELECT * (except in sub queries, or perhaps derived tables)
ALWAYS Provide a column list for INSERT Statements
ALWAYS use ANSI SQL when possible
etc..etc..etc

The list is long

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dolot
Starting Member

8 Posts

Posted - 2010-08-03 : 17:57:48

Yes, the problem went away when I selected just the columns I needed.

Unfortunately, I can't always use ANSI sql since our sql has to work with 3 different databases, and Microsoft's system.data.oracleclient objects sometimes have problems with ANSI.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources