Comments for RANK, DENSE_RANK, FIRST and LAST Analytic Functions
PIYUSH SACHAN said...The article is quite use full. try also using ROW_NUMBER fuction and see the results.
Mohamed Sami said...what does(Keep) in the DENSE_RANK mean ?
thanks for article it was helpful really
Tim... said...To quote the manual:
"The KEEP keyword is for semantic clarity. It qualifies aggregate_function, indicating that only the FIRST or LAST values of aggregate_function will be returned."
Bryan said...The article is very helpful. I didn't even know that first and last existed!
Govind said...It is very clear and excellent article about analytical functions.... Great.
Nicholas1dev said...Last request don't exposed using DENSE_RANK and FIRST.
If you remove statements 'KEEP (DENSE_RANK FIRST ORDER BY sal)' the request's result don't change.
Amy said...Please clarify why the MIN(sal) and MAX(sal) are necessary before the 'KEEP (DENSE_RANK FIRST ORDER BY sal)' in the Select clause.
Well, I'm looking for the first and last in an ordered set, so this represents the lowest and highest, so strictly speaking, the MIN and MAX themselves are irrelevant, but the syntax of an analytic function is such that I need a function of some description there, so MIN and MAX are the obvious choices. :)
Akhil said...Thanks, it helped me
Navratna said...The article is useful. Thanks
Saurabh said...nice article..
Mary said...Thanks, a very clear article.
Akshay said...partition by clause is optional
As stated in the article, the analytics syntax is explained in full in the linked article.
Anonymous said...Towards the end of the page you have FIST_VALUE instead of FIRST_VALUE
Corrected typo. Thanks.
hg said...amazing article - this was the only article with enough examples to make some sense on how to utilize these functions. kudos to you!!!
Thank you very much!
mathguy said...I don't understand the comment about using FIRST_VALUE instead of FIRST (for example). I do understand it if values were distinct (although even then, performance of aggregate vs. analytic will likely be different). But if there are ties, and you want for example the SUM of sales for the FIRST day on the job for each employee, how do you do that with analytic FIRST_VALUE?
Write a query to get the sum of sales per employee per day.
Partition by employee. Order by trunc(date). First value of that.
mathguy said...Of course there is a way to clone FIRST/LAST with analytic functions - it's just not as simple as "use FIRST_VALUE instead". Now we are summing per employee per day for all days, and then applying FIRST_VALUE. Will that result in partitioning by all (employee, day) pairs (overkill) and summing for days we are not interested in? I don't think the optimizer eliminates that. Why not use FIRST?
OK. The statement is probably a little strong, but for many cases FIRST_VALUE and FIRST do similar things. In find myself using FIRST_VALUE much more often than FIRST. I've changed the comment to be lest strong.
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.