-- 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 👍
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;
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
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 ?
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
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.
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;
-- 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
👉 SQL Portfolio Project Indian Census- Part 1
ua-cam.com/video/I3YvjFfn478/v-deo.html
Who are you dear brother, never seen anyone like you explaining all these advanced concepts of sql, just flawless.
Thanks appreciate your words 🙏
Brother why not you use row between in first query
Thankyou Ashutosh.
It's not easy to explain to someone these Advanced SQL concepts in simple terms
Hey thanks a lot
Hi Ashutosh Thank you so much your videos amazing content few days i learn so much knowledge your Videos Thank you so much
@@Mawa_Broos thanks
nth_value works in mysql. Thank you for your clear explanations.
Thanks
-- 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
👍
great explanation. Deserves much more views and subscribers❤
Thanks a lot
Perfectly working
Your learning skill is so impressive ..
Thanks a lot
Hi Ashutosh , Great work with this playlist. Appreciate it
Thx
Keeep it up bro🔥👏
Keep up the good work!
thx
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;
Thanks bro Your explanation in Top Notch👌
Glad you liked it
nice explaination
Very clear 🙌
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
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 ?
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
@@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
awesome
Yes we can use select top n query as an alternative of limit
Yeah,btw happy Diwali 🪔
@@AshutoshKumaryt Happy Diwali...isi trh ki informative vedios se sabki life Roshan krte rho..😊
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.
can we directly us ROW NUMBER function With PARTITION BY clause to get the nth value??
Can you please make the same video using postgreSQL or the Redhift please?
Hi, I'm still not sure why not whole partition by data is available for last_value()?
Hi for last value desecding order sorting will not work?
Will work
What if instead of first/last it gets replaced with min/max
You can try and see
THis is so confusing and not so clear...!!
Watch for 5 times at first it will be confusing
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;
Aabe Hindi nahi aati ky
Pranish ji ! aati ha Hindi
-- 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
thx