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

[@AshutoshKumaryt]

👉 SQL Portfolio Project Indian Census- Part 1
th-cam.com/video/I3YvjFfn478/w-d-xo.html

[@ankursharma1461]

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

[@AshutoshKumaryt]

Thanks appreciate your words 🙏

[@OppoTeam-u7s]

Brother why not you use row between in first query

[@shrutibajaj7249]

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

[@AshutoshKumaryt]

Hey thanks a lot

[@vanyagarg3331]

great explanation. Deserves much more views and subscribers❤

[@AshutoshKumaryt]

Thanks a lot

[@Mawa_Broos]

Hi Ashutosh Thank you so much your videos amazing content few days i learn so much knowledge your Videos Thank you so much

[@AshutoshKumaryt]

@@Mawa_Broos thanks

[@perumalbalachandranjj2358]

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

[@AshutoshKumaryt]

Thanks

[@alokpatra2512]

Your learning skill is so impressive ..

[@AshutoshKumaryt]

Thanks a lot

[@markhausmann3394]

-- 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
👍

[@kishanbhise4733]

Hi Ashutosh , Great work with this playlist. Appreciate it

[@AshutoshKumaryt]

Thx

[@priyanshitrivedi5605]

Keep up the good work!

[@AshutoshKumaryt]

thx

[@akashkukreti09]

Keeep it up bro🔥👏

[@adarishanmukh4583]

Thanks bro Your explanation in Top Notch👌

[@AshutoshKumaryt]

Glad you liked it

[@financetrainer]

Perfectly working

[@PamTiwari]

Very clear 🙌

[@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;

[@sonukumar-lf1qh]

nice explaination

[@abhimanyutiwari100]

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

[@bittupaul9455]

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.

[@siddhantgupta5301]

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

[@abhinavsingh706]

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 ?

[@AshutoshKumaryt]

Rank would first provide the rank the whatever rank value you want you would need to to put in a temp table or subquery to filter out that rank value, while the above functions saves that extra step

[@PamTiwari]

@@AshutoshKumaryt yes for nth value we can use Sub-query, dense_rank() and in outer query we have to put where condition like dense-rank = 5

[@BI-Rahul]

awesome

[@nirmalyapratap9927]

Hi for last value desecding order sorting will not work?

[@AshutoshKumaryt]

Will work

[@BI-Rahul]

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

[@geetaarora6721]

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

[@AshutoshKumaryt]

Yeah,btw happy Diwali 🪔

[@geetaarora6721]

@@AshutoshKumaryt Happy Diwali...isi trh ki informative vedios se sabki life Roshan krte rho..😊

[@userzzzabc]

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

[@AshutoshKumaryt]

You can try and see

[@abhishekkumar-official2650]

why you left making youtube videos

[@shreyanvinjamuri]

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

[@AshutoshKumaryt]

Watch for 5 times at first it will be confusing

[@ajitmission3227]

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

[@vijayavenkatasatyaavinashk7760]

we can find the nth value like below
SELECT *,
FIRST_VALUE(sales) OVER (partition by state ORDER BY date) AS first_value,
LAST_VALUE(sales) OVER (partition by state ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value,
(SELECT sales FROM dbo.value s2 where s1.state = s2.state ORDER BY date OFFSET 2 /*n-1*/ ROWS FETCH NEXT 1 ROWS ONLY) AS nth_value
FROM dbo.value s1;

[@pranishsonone7839]

Aabe Hindi nahi aati ky

[@AshutoshKumaryt]

Pranish ji ! aati ha Hindi

[@markhausmann3394]

-- 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

[@AshutoshKumaryt]

thx