 Hello friends, welcome to SS Unitech. My name is Susheel Singh and this is continuation of SIKO server interview questions and answers video series. So, today we are going to discuss what are the real-time use of row number except delete duplicates. So, there are a lot of articles available on internet by which you can see row number is used to delete duplicates. But here we have totally different scenario. So, let's move to another slide. So, this is your slide. As you can see we have input table that contains four columns. Our first column is member number, second column is account number, third column is entered and last column is amount. So, this table contains the information of account transactions. So, in our output table again we have four columns. First is member number, second is account number, fourth is entered it and last one is amount. So, all these columns which is our label in our input table are reflected in our output. But we want to get the latest payment informations like when we talk about member number 1 and account number 1 then we have two rows in our input table and first rows entry date is 29 and second rows entry date is 30. So, 30 is the latest payment date. So, we want to consider in our output. When we talk about member number 2 and account number 1, then in our input table we can see again we have two rows. First rows entry date is 29 and second rows entry date is 31st. So, 31st is the maximum entry date. So, this row will move to our output and when we talk about member number 2 and account number 2 and member number 3 and account number 3 then we have only single row in our input table. So, these rows will move to in our output table. So, how we can achieve this? So, first we can use aggregate function with group by to get the output but this query will not good for the performance as we are going to deal with account transaction table because this is very heavy table and it contains millions of rows. So, how we can achieve this without using group by and aggregate functions. So, here we can use row number and row number is also very good for the performance. So, let's move to SQL Server Management Studio to write the code. As I am using SQL 2014, you may have another versions of SQL Server but query would be same for all the versions of SQL Server. So, here we need to create the table then we need to execute this creative statement. So, command completed successfully. So, table has been created and then we need to insert record on it. So, we need to execute this query. So, six rows affected. So, in our slide as we can see we have six rows. So, these two has the same metadata. So, we can select and we can compare if required then the account history and here. So, how we can write query to get the output. So, first of all we need to create row number. So, we are going to use row number and then we need to specify over as this is a syntax for row number and here we want to partition by on the behalf of member number and account number. So, we have to do the partition. So, partition by then member number and account number and then we need to apply order by with this entry date. So, this is our order by with entry date and we want to by decreasing order and we can specify alias name with rw. So, here we can execute and we can check our output. So, here we can see we are getting. So, this is our first row and we can see member number one and account number one. We are getting entry date 30 and amount 140 and row number is one. So, in our output we can see 140 is our amount and entry date is 30. So, we are getting where row number is one. So, this is our first row and when we talk about our second row when member number two and account number one. So, this is our member number two and account number one. So, it's going to return entry date is 31st and amount is 480 and we can compare. So, entry date is 31st, 7th and 480 is our amount. So, we are getting same metadata. So, these two rows are our label in our output table. So, this is our output table and we can see we are getting same metadata. We can do the filter by row number. So, we can use city. So, this is our width then city and as and we can close the parentheses and here we can select by the city. So, this is our city and we can apply filter where row number is equals to one. Then it will reflect the latest payment information. So, it's going to return four rows which we have seen in our slide. So, if you are not familiar with city, then we can move this city with our drive table. So, this is our drive table and we can move it over here and here we can use the select elastic form and we want to get the data from this drive table. So, we have to specify an agas name with where row number is equals to one and we can execute this query. So, here we are getting same data as we have specified with city. If you are not familiar with city, then you can use the or second query. So, I hope you have understand how we can use the row number and it's very good for the performance because here we are not going to apply group by or aggregate when we are dealing with very big table but it's very bad for the performance by which we are going to apply group by and aggregate functions. So, we can apply row number to get the output and it's very good for the performance. So, thank you so much for watching this video. If you like this video, please subscribe our channel to get many more videos. If you need this script, it will be available on my Facebook page. You can copy from there. Thank you so much for watching.