mysql - Not getting Proper rank for Rank() Partition By DateTime -

mysql - Not getting Proper rank for Rank() Partition By DateTime -

i trying rank using below query. don't know struck up. help me ?

select id,cmno,plat,plon,rdtime ,rank() on (partition cmno,plat,plon,rdtime order rdtime) ranks tblmaeh with(nolock) cmno = 69 , rdtime between '2014-10-19 00:00:00.000' , '2014-10-20 23:59:59.997'

create table #test (id int,cmno int,plat numeric(22, 6),plon numeric(22, 6),rdtime datetime) insert #test values (8630,69,19.08491,72.83919,'21 oct 2014 14:38:13') insert #test values (8631,69,19.08491,72.83919,'22 oct 2014 18:38:13') insert #test values (8632,69,19.08491,72.83919,'20 oct 2014 14:39:13') insert #test values (8633,69,19.08491,72.83919,'20 oct 2014 14:38:13') select id,cmno,plat,plon,rdtime,rank()over (partition cmno, plat, plon, rdtime order rdtime) ranks #test with(nolock) cmno = 69 , rdtime between '2014-10-19 00:00:00.000' , '2014-10-25 23:59:59.997' drop table #test

due millisecond part of datetime rank didn't 1 selected row marked reddish mark.

i.e ex.for id 8630 rdtime value 20 oct 2014 14:38:13

might actual value of rdtime field 20 oct 2014 14:38:13.000 , next row millisecond part diffrent. rank doesn't same 4 rows.

so please eliminate millisecond part of rdtime field , seek requery same sql.

mysql sql-server-2008 sql-server-2005 sql-server-2008-r2