first_value and last_value SQL window functions MASTER CLASS | Advance SQL

In this video we are going to discuss 2 important SQL window functions first value and last value. We will discuss them in detail and talk about some important issues with the last value function.
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
script:
create table employee(
emp_id int,
emp_name varchar(20),
dept_id int,
salary int,
manager_id int,
emp_age int
);
insert into employee values(1,'Ankit',100,10000,4,39);
insert into employee values(2,'Mohit',100,15000,5,48);
insert into employee values(3,'Vikas',100,10000,4,37);
insert into employee values(4,'Rohit',100,5000,2,16);
insert into employee values(5,'Mudit',200,12000,6,55);
insert into employee values(6,'Agam',200,12000,2,14);
insert into employee values(7,'Sanjay',200,9000,2,13);
insert into employee values(8,'Ashish',200,5000,2,12);
insert into employee values(9,'Mukesh',300,6000,6,51);
insert into employee values(10,'Rakesh',500,7000,6,50);
#sql #advanced

[@SKAMRANPASHA]

Make video on different functions, like COALESCE, EXIST, INTERVAL, CONCAT, EPOCH etc

[@rishav144]

you are right .....

[@jasleenkaur-cw2fw]

Awesome explanation! Thanks for sharing

[@rockagain5416]

Whenever I watch your video I really Learn New Things Thankyou Sir❤❤

[@SKAMRANPASHA]

Bahot achche !

[@avi8016]

Good to know the difference between first and last value.
Thanks a lot sir🙏

[@ankitbansal6]

Keep watching

[@sreemalapal]

The perfect explanation which I was searching till date over the internet reg the issue with Last Date func !! Thank you 😀😀

[@ankitbansal6]

Great to hear!

[@baali735]

Thank you new thing for me

[@equiwave80]

Thanks for this video. It was super useful!!!

[@ankitbansal6]

Glad it was helpful!

[@pujasil2087]

I learn something new today. Thanks for this. It is really helpful

[@ankitbansal6]

Glad it was helpful!

[@DataEngineerPratik]

Great !!

[@AdharshSunny]

Thanks Brother for the Continues great Work.
Just asking if you can start any GCP Engineering concepts also like Dataflow, composer

[@Sridharkc_]

Hey Ankit!!
You are giving me hope in technical aspects video by video

[@ankitbansal6]

All the best

[@andreagreco7574]

Useful as always, thanks Ankit. The difference between the two functions is tricky, thanks for bringing that up.

[@ankitbansal6]

Glad it was helpful!

[@norbertlipinski7598]

Actually it is not about how the functions work as they work both in exactly same way, the confusion is caused by how the ORDER BY clause within window function changes the data frames which are being taken to the functions to calculate the result for each row.

[@khushishrivastava331]

Hello Ankit,
Thank you for making such insightful videos.
I want to ask a question here.
I have come across in a lot of interviews that they ask to solve a question without using a window function. Is there any trick to solving such questions? Can you provide us with an example.

[@vishwish123]

Thank you so much Ankit Sir your explanation is fantastic.......😊

[@ankitbansal6]

Most welcome 😊

[@SantanuRoy-oq2nv]

Fantastic Information. Thank you Ankit

[@ankitbansal6]

Thanks and welcome

[@SACHINKUMAR-px8kq]

Thankyou So much Sir for this Amazing Guidance

[@ankitbansal6]

Most welcome

[@inspiredomkar1239]

Sir,It would be great if you take a SQL query where we have to make use of these functions.

[@ankitbansal6]

Next video

[@chillavenkatesh6097]

Hello Ankit,
We can also use Join by 'using' keyword.
For suppose, generally we write join like : a join b on a.id=b.id
So by using keyword we can write like: a join b using id
So many of them don't know that 'Using' keyword.
Can you please make a video on it.
And yeah i had used that first_value and last_value function

[@anirbansarkar6306]

Great Tutorial. Thanks Sir

[@ankitbansal6]

You are most welcome

[@kanchankumar3355]

Good one, nicely explained 👍

[@ankitbansal6]

Glad you liked it

[@amazingriknow5986]

Thank for providing script in description Ankit.
Also kindly try to do atleast 3videos per week . Just a request.
Thank you

[@ankitbansal6]

I will try my best

[@amazingriknow5986]

@@ankitbansal6 thank you

[@nikhilmogre206]

I liked the explanation part
Thank u😊

[@ankitbansal6]

You're welcome 😊

[@abhinavkumar-jj2dx]

wow....Literally Awesome :)

[@ankitbansal6]

Thank you! Cheers!

[@vineetjain7518]

Applied in example

[@vaibhavverma1340]

Very well Explained :)

[@ankitbansal6]

Thank you 🙂

[@abhimanyutiwari100]

Make a video on EXIST

[@clear_vision_]

Yet another great video Ankit. Just a doubt I had in the frame clause : last_value(emp_name) over(order by emp_age rows between current row and unbounded following)
last_value(emp_name) over(order by emp_age rows between unbounded preceding and unbounded following)
Both above frame clauses give same results, right?

[@ankitbansal6]

Yes

[@ThePragyashree]

Today i was studying for an interview.. and i was not understanding this concept.. thank you for this topic 🙏

[@ankitbansal6]

All the best

[@greatromulus3028]

if we can order by asc or desc why we need last_value function? we can bring last_value just adding desc at the end of the order by.

[@ankitbansal6]

Exactly. We don't need it. 😊

[@rakeshbabu3839]

In the LAST_VALUE function..you used ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.....does this work on WINDOW FUNCTION we use or both on ORDER BY and WINDOW FUNCTION we have used?

[@ankitbansal6]

Yes all aggregation functions it works

[@rakeshbabu3839]

@@ankitbansal6 Thanks 🙏🏻

[@rakeshbabu3839]

At 7:39 ...why you again added order by emp_age at end of query even though you used order by in OVER clause.
Even if you didn't add order by at the end the output looks same right

[@ankitbansal6]

That's just to sort the result in output. Even if you don't do it the result will be same

[@rakeshbabu3839]

@@ankitbansal6 okay..thanks sir

[@Hkumar_new]

Anable to see any thing video