Maxium rownum groupwise
-
create table #Abc(ID int,Name varchar(20),SAl int,Rownum int)
insert #Abc values(1,'Anees',1000,1)
insert #Abc values(2,'Rick',1200,1)
insert #Abc values(3,'John',1100,1)
insert #Abc values(3,'John',1500,2)
insert #Abc values(3,'John',1200,3)
insert #Abc values(4,'stephen',1300,1)
insert #Abc values(5,'Maria',1400,1)
insert #Abc values(6,'MariaN',1400,1)
insert #Abc values(6,'MariaN',60000,2)
O/p:
ID Name Sal Rownum
1Anees1000.00 1
2Rick1200.00 1
3John1100.00 1
3John1500.00 2
3John1200.00 3
4Stephen1300.00 1
5Maria1400.00 1
6MariaN1400.00 1
6MariaN60000.00 2
so i need the table like the given below
ID Name Sal Rownum
1Anees1000.00 1
2Rick1200.00 1
3John1200.00 3
4Stephen1300.00 1
5Maria1400.00 1
6MariaN60000.00 2
so plz help me
-
WITH CTE AS (SELECT ID ,Name ,SAl,Rownum,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Rownum DESC) AS rn
FROM #Abc)
SELECT ID ,Name ,SAl,Rownum
FROM CTE
WHERE rn=1
ORDER BY ID;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
No need to add an (extra) row_number, because there is allready a rownumber defined and only the maximum rownumber is needed in the result
select
#abc.*
from #Abc
inner join
(select ID, MAX(rownum) as rownum from #Abc group by ID) sub
on #Abc.ID = sub.ID
and #Abc.Rownum = sub.rownum
order by #abc.id
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply