First Value in SQL | Last Value SQL | Nth Value SQL | Functions Advanced SQL | Ashutosh Kumar

Sql one of the most important language asked in most of the analytics interviews,in this series i have discussed some advanced level sql concepts that are frequently asked in data analyst,business analyst interviews. In this video i have covered first value and last value concepts in sql which comes in window functions.

👉 Link to excel file -

👉 Complete playlist on Sql Interview questions and answers

---------------------------------------------------------------------------------------------------------------------
Check out some more relevant content here

👉 How to Learn SQL

👉 How to become a business analyst complete roadmap-

👉 How to become a data analyst complete roadmap-

👉 Top 3 you tube channels to learn sql for free for beginners

👉 Rank ,Dense Rank, Row Number in sql -

👉 Cross join in sql

👉 union join in sql

👉 left join in sql

👉 Right join in sql

👉 Inner join in sql

👉 Introduction to tables and databases in sql -

👉 Aggregate Function in sql

👉 Functions in sql-

👉 String Function in sql

👉 CRUD operations in sql

👉 Autoincrement in sql

👉 Primary Key in sql-

👉 Null and Default values in sql-

👉 Data types in sql-

____________________________________________________________________

_______________________________________________________________________
Connect with me

_____________________________________________________________________

Comment down if you have any doubts
Please leave a LIKE 👍 and SUBSCRIBE ❤️ to my channel to receive more amazing content in data analytics and data science.

_____________________________________________________________________

🏷️ Tags

sql,
sql for data science,
sql for data analytics,
sql practise questions,
sql practise questions and solutions,
sql tutorials for beginners,
sql problems for data engineers,
ashutosh,
ashutosh kumar,
ashutosh kumar analytics,
sql problems easy,
sql problem medium,
sql problems hard,
sql window functions,
sql advanced questions,
rank functions in sql,
lag lead in sql,
sql interview questions and answers,
sql interview questions,
sql questions asked in interviews,
hackerrank sql solutions,
hackerearth sql solutions,
leetcode sql solution

🏷️HashTags

#sql #interviews #questions #solutions

[AshutoshKumaryt]

👉 SQL Portfolio Project Indian Census- Part 1

[ankursharma]

Who are you dear brother, never seen anyone like you explaining all these advanced concepts of sql, just flawless.

[shrutibajaj]

Thankyou Ashutosh.
It's not easy to explain to someone these Advanced SQL concepts in simple terms

[perumalbalachandranjj]

nth_value works in mysql. Thank you for your clear explanations.

[vanyagarg]

great explanation. Deserves much more views and subscribers❤

[markhausmann]

-- You are right. NTH_VALUE is indeed not implemented in SQL Server by now.
-- Even with the most recent version this feature is not present.
-- But I think you can work arround this limitation by combining FIRST_VALUE with LAG.
-- Based on your example the solution should look like this.
-- Greetings and thumbs up You are a good teacher !


;WITH c1 AS (
SELECT
*,
n2_day_sales = LEAD(Sales, 1) OVER ( PARTITION BY State ORDER BY Date ),
n3_day_sales = LEAD(Sales, 2) OVER ( PARTITION BY State ORDER BY Date )
FROM
first_value_last_value
)
SELECT
*,
first_day_sales = FIRST_VALUE(Sales) OVER ( PARTITION BY State ORDER BY Date ),
last_day_sales = LAST_VALUE(Sales) OVER ( PARTITION BY State ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ),
second_day_sales = FIRST_VALUE(n2_day_sales) OVER ( PARTITION BY State ORDER BY Date ),
third_day_sales = FIRST_VALUE(n3_day_sales) OVER ( PARTITION BY State ORDER BY Date )
FROM
c1
ORDER BY ID







👍

[alokpatra]

Your learning skill is so impressive ..

[kishanbhise]

Hi Ashutosh, Great work with this playlist. Appreciate it

[adarishanmukh]

Thanks bro Your explanation in Top Notch👌

[bittupaul]

Ashutosh, can we use rows between current row and unbounded following for last value? As partition is sorted by dates, last value has to be last row in the partition and it doesn't matter which row, we start, last value will always come from last row.

[abhinavsingh]

We can use Top in sql server in place of Limit.

Also, first or nth values can also be queried by rank/row_number function.
Any special scenarios where first/last_value will be useful ?

[shubhankarsatvaya]

for the 5th day you can use the dense rank

SELECT empid, state, date, salary
FROM (
SELECT empid, state, date, salary
DENSE_RANK() OVER (PARTITION BY state ORDER BY salary DESC) AS rank
FROM employee
) AS ranked_salaries
WHERE rank = 5;

[siddhantgupta]

can we directly us ROW NUMBER function With PARTITION BY clause to get the nth value??

[abhimanyutiwari]

Hi, I'm still not sure why not whole partition by data is available for last_value()?

[BI-Rahul]

Can you please make the same video using postgreSQL or the Redhift please?

[geetaarora]

Yes we can use select top n query as an alternative of limit

[ajitmission]

select *,
FirstValue = first_value(Sales) over(partition by state order by date ),
LastValue = first_value(Sales) over(partition by state order by date desc)
from dbo.Running_Sum_DataA

This query is also working fine for last value. why we will large query

[nirmalyapratap]

Hi for last value desecding order sorting will not work?

[userzzzabc]

What if instead of first/last it gets replaced with min/max

[shreyanvinjamuri]

THis is so confusing and not so clear...!!