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

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

rewrite - Trouble with Wordpress multiple custom querystrings -