Hold on , why have you used binary datatypes?

Create a unique index on the column , and stop the issue happening in the first place

If this doesnt work please post DDL , so that i can test it .

with ct(subject_id,time_in1)

as

(

select subject_id,

convert(datetime,left(History.Time_In,2) + ':' + substring(History.Time_In,3,2) + ':' + right(History.Time_In,2),108)

as time_in1

from history

)

select subject_id, max(time_in1)

from ct

Please see this link

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

I would urge you to store the data as a datetime column on your table which make sense for a whole lot of reasons. But this should answer your question

with...

Build a calendar table and outer join from that to your real data.

Take a look at this Article

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

create table yourtab

(

id integer,

Flag char(1)

)

go

insert into yourtab values(1,'T')

insert into yourtab values(1,'F')

insert into yourtab values(1,'T')

insert into yourtab values(2,'T')

insert into yourtab values(2,'T')

insert into yourtab values(2,'T')

insert into yourtab values(3,'F')

insert into yourtab values(3,'F')

insert into...

Hows this ?

with cteResults(Name)

as

(

Select top 100 Name

from sysobjects

where Name like 'a%'

)

Select count(*) from cteResults

If proper design had been used it would of been fairly easy.

But Ill simulate it with view.

I bet you have fun on the first of each month with the 'ALTER...

This maybe a bit over the top for what you require, but it does handle extra spaces in the string ....

drop table #t1

go

create table #t1

(

RowId integer,

ValueStr nvarchar(50)

)

go

insert into #t1 values(1,'1,...

Top is processed on the result set , so you wont get the results you are expecting

http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-%E2%80%93-logical-query-processing-phases-%E2%80%93-order-of-statement-execution/

Is this what you are after ?

select case when count(*) >100 then 100 else...

drop table #t1

go

create table #t1

(

RowId integer,

ValueStr nvarchar(50)

)

go

insert into #t1 values(1,'1,2,3,4')

insert into #t1 values(2,'4')

insert into #t1 values(3,'25,26,27,28,29,30,31,32')

insert into #t1 values(4,'2')

go

select * from #t1 where ValueStr = '2'

go

select * from #t1 where...

Still not clear ...

Please post a script to create the table and insert values

Ive got a bad feeling about this

http://www.sqlservercentral.com/Forums/Topic775882-338-1.aspx?Update=1