sql server - SQL max(DateTime) and category filter without group by -
i've written below query :
select datetime, configid, rowid linkedtabledefinition a, inner join tabledefinition b, on a.target = b.id inner join viewwithinfo c, on a.target = c.id
this gives following output:
datetime configid rowid 12-09-2013 11:00 4 12 12-09-2013 12:00 4 12 12-09-2013 13:00 3 11 12-09-2013 12:00 3 11 12-09-2013 11:00 4 11
what need of output following: per rowid , configid combination need highest value datetime column. above example want following output:
datetime configid rowid 12-09-2013 12:00 4 12 12-09-2013 13:00 3 11 12-09-2013 11:00 4 11
does know answer? avoid group because select statement extended lot more columns.
thanks in advance
edit current query:
select testresults.resultdate, testresults.configurationid, testresultstestcaseid dbo.factworkitemlinkhistory workitemlink inner join dbo.dimworkitem workitem on workitem.system_id = workitemlink.targetworkitemid inner join dbo.testresultview testresults on testresults.testcaseid = workitemlink.targetworkitemid removeddate = convert(datetime, '9999-01-01 00:00:00.000') , workitemlink.sourceworkitemid = 7 , workitem.system_workitemtype = 'test case'
select * ( select datetime, configid, rowid, row_number() over(partition configid, rowid order datetime desc) rownum linkedtabledefinition inner join tabledefinition b on a.target = b.id inner join viewwithinfo c on a.target = c.id ) src src.rownum = 1
Comments
Post a Comment