sql server - SQL Framing Query Problem -

i running sql query returns portion of entire data. in other words, data 'framed.' have 'order(order by' part , whatever reason, part isn't working of time. expect, , other times don't.

the query:

select      * (     select         row_number() over(order [datetime] desc, timemicrosec desc) rownum,         ...             ...             ... ) temptbl     rownum between @startrow , @endrow; 

the whole data query works when not framed , use 'order by' clause @ end. in image below, [datetime] column , [timemicrosec] column joined string concatenation.

as can see, ordering messed up. appreciated.

when cast datetime varchar, changes way sql server order column. no longer order chronologically, instead plain old string.

if data type datetime, following descending sort order:

01/11/2011 02/22/2010 

the first date later chronologically... if data type varchar... sorted as:

02/22/2010 01/11/2011 

because string "02" comes after "01"... actual date value doesn't matter @ point. when concatenate date timemicrosec, change sorting varchar sort.

like others said... if order rownum instead of concatenated string, chronological order.