Oracle PL SQL interview question RANK and DENSE RANK as Aggregate Function and Analytical Function

hello friends welcome to the video series on interview question for SQL pl/sql developers in this video we'll see about the ranking functions like rank and den strength and how to use these ranking function as aggregate function and analytical function so before we start understanding how to use these as aggregate and analytical function let us understand the ranking methodology okay so for our easier understanding I have an employee table which consists of like a 14 employees and I have just ordered it by a salary in descending order so that it is easy for us to rank suppose in this case if you want to give a rank based on his salary typically we'll start with the maximum salary we would rate the king with 5,200 salary as the rank rank 1 followed by there are two people who are getting 3,000 as the salary so we will since there are records dying in this particular salary will give the same rank to both the person like for Scott and for the salary is 3000 so typically we will give second rank for both the person since we have given the second rank for two people typically we will keep the next rank and for John will typically start with rank as for so followed by the Blake s rank foil followed by the clerk s rank six followed by Alan as rank 7 and again there are two people who are sharing the same salary that is thousand five hundred so for Miller and Turner will be giving the rank as eight since we have given the same time for two people we will skip the next rank so the next rank we'll start with 10 and again the next time position also is taken bit like 2% so we are just giving 10 for two people that's why we are skipping the next track so the next round would be like 1213 followed by 14 so this is like one way of ranking let us call it as like a rank methodology there is another way by which will be ranking of course it is exactly similar but whenever there is a tie or whenever two people takes the same ranking in the next approach typically we will not skip the ranking in the second method the ranking will be just continuous for example the first record first rank will be obviously one followed by two since Scott and fourth I took the has got the same salary in this case then we will not be skipping the next salary instead of giving four here we will just start with three so in this approach the bank will be just continuous it's like densely-populated it's it's not like a discontinuity rank since it is like a continuous in this case we will call it like a dense rank so these are the two ways by which generally the ranks are computed to achieve this we have like two functions one is called rank which typically to compute the first methodology of rank and another function is called bins underscore rank to compute this second way so in fact the difference between a rank and dense rank is very simple in rank the rank may not be continuous if there is a tie same thing in dense rank that then ranking will be the continuous there will not be any discontinuity the best example is this portion okay in here the rank is like discontinuity after two there is no person getting third rank whereas in the dense rank the rank is slave continuous so these are the two functions now let us understand how to use a rank and dense rank function as an aggregate function and as an analytical function so first we will start with the aggregate function so we know that we have like a lot of aggregate functions like a min max average sum count all these things are aggregate function a typical definition for a very gate function is like a real gate function typically takes like multiple row of input and do the aggregation just return one single value same thing like here rank can be used either as an aggregate function or as an analytical function when we are using as an aggregate function rank will help us to compute the rank of a particular value in the given data set for example if you want to compute the rank of thousand six hundred okay typically we need to find all the rank then find the rank of a thousand six hundred instead of that by using the rank as well as dense rank as an aggregate function we can find this find the rank of the particular number in the given results and let us understand better with an example now so here is the same data set okay so the syntax for using the rank as an aggregate function is as I mentioned we nee we are going to find the rank of a particular member so in this case I am just taking thousand six hundred so rank of thousand six hundred width and group is a keyword ordered by salary descending obviously rank we will be able to compute in an ordered group right so this will just compute until what is the rank of a number in fact we can use like other columns also suppose if I say Commission we can use like salary comma Commission also so that means for this particular group it will just take what is the rank so for right now just leave only the salary portion so this is about so this is how we will be computing a rank for a particular value exactly holds good for dents right however dense rank up computes the dense rank value if you can see here rank is seven whereas dense rank a six that's what we have already computed for Allen so this is about how we will use the rank and dense rank as an aggregate function so the key learning here is that rank and dense rank will help us to find the rank or dense rank of a particular given input value within the given order result set so that is about the rank and dense rank has an aggregate function now we will see how to use rank and dense rank as an analytical function they will understand with an example now now we will compute the rank and then rank as analytical function analytical function is like different from aggregate because here we are not going to do any aggregation analytical function we just computes the rank and display for each and every employee here in fact it compares with all the other salaries and it will just display first let us start with the rank over order by salary descending so this is about the rank of the employee in fact exactly the same thing you want to compute the din strength we just use dense rank function the advantage of analytical function is that we can do the analytical class itself will support us providing the so we can say like exactly the same thing we can use like dense rank so that so that for each department voice we'll be able to compute the salary in fact like both the ranking as well as the dense ranking so this is about the analytical function so the key learning here is that analytical function will help us to compute the rank for all the employees here it's it's bit different from the aggregate just to quickly summarize the aggregate functional analytical function whenever we want to compute a rank for a particular value in a result set we'll be using an aggregate function it's not necessary that you have to give the exactly the same value in the result set you can give any value it just computes the hypothetical rank and returns that same way like in case of an analytical function it just helped us to compute the rank as well as the dense rank for the given results it the advantage is that we can use the partition by class to do a sub partitioning within the results and we can with the ranking and dense ranking if would have learned something new please like this video subscribe and stay tuned for new feature videos interview questions still practical questions with concept video if you want any questions to be answered you can post in the comment section or you can drop to this melody thanks a lot for watching this video

Channel: Siva Academy

Views: 20,349

Rating: 4.9509802 out of 5

Keywords: pl sql interview questions, oracle sql plsql interview questions, oracle interview questions, oracle fresher interview questions, oracle sql interview questions for freshers, oracle sql interview questions for developers, sql interview questions, oracle sql, sql, plsql, RANK, DENSE_RANK, rank vs dense_rank, oracle rank vs dense_rank, rank vs dense_rank example, oracle rank over partition example, oracle dense_rank function, oracle dense_rank example

Id: CAiX1J-CIlc

Length: 9min 7sec (547 seconds)

Published: Thu Mar 07 2019