HELP with distinct

[coroll]

Hi all,

In my table i have duplicate rows. i want to get distinct rows.

select distinct * from table1 does not work.
select distinct col1,col2,col3,col4 from table1 does not work.
both say: The text data type cannot be selected as DISTINCT because it is not comparable.

select distinct(col1) col2,col3,col4 from table1 is working.But the data of col1 shows under the name of col2. The data which should come under col2 is now showing.

PLZ help me to solve this

[adam_k]

I don't have an SQL available now to test it with text fields, but try to

select col1,col2,col3,col4,count(*) from table1 
group by col1,col2,col3,col4

If you don't want the count(*) in your results, use it as a derived query as so:

select col1,col3,col3,col4
from (select col1,col2,col3,col4,count(*) as 'counter' from table1 
group by col1,col2,col3,col4) 'a'

[coroll]

I don't have an SQL available now to test it with text fields, but try to

select col1,col2,col3,col4,count(*) from table1 
group by col1,col2,col3,col4

If you don't want the count(*) in your results, use it as a derived query as so:

select col1,col3,col3,col4
from (select col1,col2,col3,col4,count(*) as 'counter' from table1 
group by col1,col2,col3,col4) 'a'

i did your first one but it says
: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

second one it sayas: incorrect syntax near 'a'
then i executed it without 'a' then it says :Incorrect syntax near ')'

this statment is writen after a sequence of statments and the table is a tempory table like #table1
when i execute 'select distinct * from #anotherTable 'alone it works. It is giving me distinct rows.

these are my statments

create table #tempKeywords
(
code int identity(1,1),
BKID int
)
declare @c int
set @c=0
select @c=count(*) from Keyword
declare @i int
set @i=1
while @c <>0
begin
declare @word varchar(20)
declare @BKKID int
select @word=keyword,@BKKID=Book_ID FROM Keyword where KeywordIndex=@i
if ((select dbo.RegExpLike(@word,'(\w*s\w*)'))>0)
begin
insert into #tempKeywords values(@BKKID)
end
set @c=@c-1
set @i=@i+1
end

set @c=0
set @i=1
select @c=count(*) from #tempKeywords
declare @BOOKID int
CREATE TABLE #tempMember
(
  Book_ID INT,
  Title text,
Num_Of_copies int,
Avalable_Copies int,
Booked_Copies_in_advance int,
)
while @c<>0
begin
select @BOOKID=BKID from #tempKeywords where code=@i
exec GetBookInformation1 @BOOKID
set @c=@c-1
set @i=@i+1
end
drop table  #tempKeywords
-- this is where the distinct problem comes
select Book_ID,Title,Num_Of_copies,Avalable_Copies ,Booked_Copies_in_advance from (select Book_ID,Title,Num_Of_copies,Avalable_Copies ,Booked_Copies_in_advance,count(*) as 'count' from #tempMember group by Book_ID,Title,Num_Of_copies,Avalable_Copies ,Booked_Copies_in_advance)
exec dropDummyTable

[adam_k]

After a couple of tries I can only think that you should create #tempMember with Title as varchar(MAX). This will allow you to select distinct.

[coroll]

i did your first one but it says : The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

second one it sayas: incorrect syntax near 'a' then i executed it without 'a' then it says :Incorrect syntax near ')'

this statment is writen after a sequence of statments and the table is a tempory table like #table1 when i execute 'select distinct * from #anotherTable 'alone it works. It is giving me distinct rows.

these are my statments

create table #tempKeywords
(
code int identity(1,1),
BKID int
)
declare @c int
set @c=0
select @c=count(*) from Keyword
declare @i int
set @i=1
while @c <>0
begin
declare @word varchar(20)
declare @BKKID int
select @word=keyword,@BKKID=Book_ID FROM Keyword where KeywordIndex=@i
if ((select dbo.RegExpLike(@word,'(\w*s\w*)'))>0)
begin
insert into #tempKeywords values(@BKKID)
end
set @c=@c-1
set @i=@i+1
end

set @c=0
set @i=1
select @c=count(*) from #tempKeywords
declare @BOOKID int
CREATE TABLE #tempMember
(
  Book_ID INT,
  Title text,
Num_Of_copies int,
Avalable_Copies int,
Booked_Copies_in_advance int,
)
while @c<>0
begin
select @BOOKID=BKID from #tempKeywords where code=@i
exec GetBookInformation1 @BOOKID
set @c=@c-1
set @i=@i+1
end
drop table  #tempKeywords
-- this is where the distinct problem comes
select Book_ID,Title,Num_Of_copies,Avalable_Copies ,Booked_Copies_in_advance from (select Book_ID,Title,Num_Of_copies,Avalable_Copies ,Booked_Copies_in_advance,count(*) as 'count' from #tempMember group by Book_ID,Title,Num_Of_copies,Avalable_Copies ,Booked_Copies_in_advance)
exec dropDummyTable

Thank you very much....
i tried for 15hrs.