Actual Practise : Row_Number() over(partition by colnum1 order by colnum2 ) as No - 7

Let us see a requirement :
we need show the Max(ID) of each loan record however each loan has one more records.

How will we do?
The best and the first way is :

Let me show you by Row_Number() Over(partition by colnum1 order by colnum2) as No to make it effective.

;with cte as(select Row_number() over (partition by servicerloannumber order by hmpmodificationdataid desc) as no, * from hmpmodificationdata where servicerloannumber in( '0015691793','0017522277','0015328560'))select * from cte where no = 1

See the results:

Let me explain:
1. partition by servicerloannumber : “servicerloannumber” is the colnum we make a group to the result;
2. order by hmpmodificationdataid desc : “hmpmodificationdataid” is the colnum we make the order to the result which has been grouped.
3. As No : we will add a number following the each record of loans. For example : If the loan has 3 records, then we will see **1,2,3**in front of the records by order which are used to identify the sum records and somewhere may be useful.

As the photograph :

Of course, if you wanna get more about it you can search it in Google or Baidu~~~

We can have some other ways to replace it but it is not the best way. However I still show you how to make it.

select * from hmpmodificationdata Awhere not exists (    select null from hmpmodificationdata B     where 1=1    and a.servicerloannumber = b.servicerloannumber     and a.hmpmodificationdataid < b.hmpmodificationdataid) and servicerloannumber in( '0015691793','0017522277','0015328560')

See the result:

Is it the same with upside?! Yes it is. I will use Chinese to explain :

1. 在exists 中，select * 与select null效果一样；
2. 在查询A表数据时，当servicerloannumber 相同时且存在hmpmodificationdataid更大（更新）时，则不要这条数据，一直排除到hmpmodificationdataid是最大的。

We all need to understand what it means.