sql - How to self query table in order to get last executed testcase from same table -

sql - How to self query table in order to get last executed testcase from same table -

i using mssql database sql server 2008 have query database

select *, datediff(n,startdatetime,enddatetime) datedifference result project='sample' , subproject='sample' , (startdatetime)>='11/01/2013 00:00:00' , (enddatetime)<='11/11/2014 23:59:59' order enddatetime desc

it list downwards entries result table. result of above query is

testcasename username startdatetime enddatetime poonam_tm_003 user1 2014-11-10 17:39:27.000 2014-11-10 17:39:31.000 poonam_tm_003 user1 2014-11-10 17:39:24.000 2014-11-10 17:39:27.000 poonam_tm_003 user1 2014-11-10 17:39:20.000 2014-11-10 17:39:24.000 poonam_tm_003 user2 2014-11-10 17:39:17.000 2014-11-10 17:39:20.000 30septestcase tm1 2014-10-29 10:12:09.000 2014-10-29 10:17:07.000 30septestcase tm1 2014-10-29 10:06:10.000 2014-10-29 10:09:41.000

i want enries such means lastly executed testcase

testcasename username startdatetime enddatetime poonam_tm_003 user1 2014-11-10 17:39:27.000 2014-11-10 17:39:31.000 30septestcase tm1 2014-10-29 10:12:09.000 2014-10-29 10:17:07.000

i need lastly entires each test case. mean testcase has max(enddatetime)

here reply might help:

select testcasename, username, startdatetime, enddatetime, datediff(n, startdatetime, enddatetime) datedifference (select *, row_number() on ( partition testcasename order enddatetime desc) rn yourtable) t project = 'sample' , subproject = 'sample' , ( startdatetime ) >= '11/01/2013 00:00:00' , ( enddatetime ) <= '11/11/2014 23:59:59' , rn = 1

sql