Get maximum of RevNo and Patition by Menu and VouNo

Question

Hi,
I need maximum of RevNo partition by Menu and VouNo

CREATE TABLE #temp (Menu varchar(10),VouNo varchar(20),RevNo INT,UserName varchar(30))
INSERT INTO #temp (Menu,VouNo,RevNo,UserName)
VALUES ('M101','1001',0,'User1')
,('M101','1001',1,'User2')
,('M101','1001',2,'User3')
,('M102','2001',1,'User1')
,('M103','3001',1,'User2')
,('M103','3001',2,'User1')
,('M103','4001',1,'User1')

SELECT Menu,VouNo,max(revno) AS RevNo FROM #temp group by Menu,VouNo  

DROP TABLE #temp  

I want exact output like

Answer 1

If you do not need anything special, then try the eternal row_number:

;  
with Q as  
(  
    select *, row_number() over (partition by Menu, VouNo order by RevNo desc) rn  
    from #temp  
)  
select Menu, VouNo, RevNo, UserName  
from Q  
where rn = 1  
order by Menu, VouNo