Row_number in SQL Server 2005

An article on Rownumber:



Usually we used to find some peculiar scenarios like below,


An employee is frequently shifting to different places. I need to fetch all the employees recent location. Considering an employee is working in texas, a record will be added. Now, the employee got shifted to newyork. I need to list the employees recent location. How can i achieve it

create table venkat1
(id int, name1 varchar(100),name2 varchar(100))
insert into venkat1 values(1,'Venkat','Venkat1')
insert into venkat1 values(2,'Venkat1','Venkat2')
insert into venkat1 values(3,'Venkat1','Venkat3')
insert into venkat1 values(4,'Venkat','Venkat4')
select * from venkat1

select id,name1 from
(select id, row_number() over (partition by name1 order by id desc) as row, name1
from venkat1 )tbl where row=1




Thanks and Regards,
Venkatesan Prabu .J