FIRST_VALUE and LAST_VALUE : Problem Solving using Analytic Functions

[@keifer7813]

Fantastic video. Loved the explanation, and demonstrations. This is what you call teaching 👏

[@Adam_Marley]

Wonderful Video! Thank you for your excellent clarification on this (what I find tricky) topic

[@desarai8284]

very grateful for this video. thank you!

[@hm.g.2685]

Thanks a lot. In just few minutes I get to understand a very complex topic for me. Really appreciated your efforts.

[@gouthamanush]

Hi Tim, I didn't understand why the "range between unbounded preceeding and current row" is not applicable for first_value while it is applicable for last value. Could you please explain me that. Thanks.

[@oracle-base]

You can still use the windowing clause, but for FIRST_VALUE is doesn't matter, because it is only ever looking at the first row in the partition, so the window of action only ever really matters about touching the first row. The default windowing clause of RANGE BETWEEN UNBOUNDED PRECEEDING TO CURRENT ROW means FIRST_VALUE always hits the first row. In contrast, LAST_VALUE will not see the last row of the partition unless you explicitly give it a windowing clause.

[@gouthamanush]

@@oracle-base got you. Thanks for the reply. I bookmarked your website long time ago.. your videos really helped me alot.

[@oracle-base]

"range between unbounded preceeding and current row" is the default windowing clause, so once you user ORDER BY in the analytic clause you get "range between unbounded preceeding and current row" by default, so you don't have to add it or change it. When using LAST_VALUE, you need to see all values in the partition to actually pick up the last value. Unless you open the window to the whole partition, you will be getting the last value of a subset of the partition data.

[@camilovega4914]

Many thanks for the explanation!!

[@BryanSheasby]

I like the video but seeing that is is and it has now been ( - 2006) years since the release of SQL Developer, could you use that for your videos? The main reason being the ability to display the SQL and result in the same window.

[@oracle-base]

This is just a preference thing. SQL Developer is a great development tool, but I don't think is works well visually for videos or presentations. I find it rather distracting, and wasteful of screen space. Of course, some people would not agree with me. :) I tend to do the videos the way I present, which is with SQLcl. I just feel it works better for me. Can't please all the people all the time... :)

[@BryanSheasby]

@@oracle-base I understand. I mostly just want to be able to pause the video and see the SQL and the output at the same time.

[@oracle-base]

@@BryanSheasby My solution. Run it twice and pause at two different times. I get double the views. KZbin domination. Muhahaha! Seriously though, most of my videos are based on an article of the same/similar name from my website (links in description). You can pretty much have that open if you like, which shows the query and the result together. In this case: oracle-base.com/articles/misc/first-value-and-last-value-analytic-functions

[@SunilKumar-bv9nj]

EXCELLENT I 'M GREAT FULL TO YOU

[@hm.g.2685]

I would appreciate of you can spare sometime to explain "range between unbounded preceeding and current row" with more complex examples. In case you already did that I appreciate pointing me to such a video.

[@oracle-base]

I explain it in more detail here. oracle-base.com/articles/misc/analytic-functions#windowing_clause

[@lakshya209]

Thanks mate!