LAST_VALUE Analytic Functions in SQL Server - #4 #SQLServer #LASTVALUE #SQLAnalytics #DataAnalysis

#SQLServer #LASTVALUE #SQLAnalytics #DataAnalysis #TSQL #SQLFunctions #DataScience #Database #SQLQuery #SQLTips #DataEngineering #BigData #Analytics #SQLServer2019 #SQLServer2022 #TechTips #Programming #DataVisualization #BusinessIntelligence #sqlperformance

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

QUERY SOLVE -
1. What is analytic functions in SQL Server?
2. What is LAST_VALUE analytic functions in SQL Server?
3. Syntax of LAST_VALUE analytic functions?
4. What is partition by clause in SQL Server?
5. What is Order by clause in SQL Server?
6. Default parameters of LAST_VALUE analytic functions in SQL Server?

LAST_VALUE()
------------------------
LAST_VALUE function return the last value in an ordered set of values. Return type of this function is same type as scalar_expression.

Syntax :
LAST_VALUE ( [ scalar_expression ] )
OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.

order_by_clause determines the logical order in which the operation is performed. order_by_clause is required.

rows_range_clause further limits the rows within the partition by specifying start and end points.

[cosmicdreamer]

One important point was missed! When the " ROWS Between UNBOUNDED PRECEDING... clause " is not used, values aren't just INCORRECT, They occur in a way where 2 or more Values are the same for sets partitioned by ORDER BY clause, and then the last row value is taken. Here you can see that for Row 1 we have the last_value as Row 1 and for Row 2 we have the last_value as Row 2 but for Row 3 and 4, the last_value is taken as the Name of Row 4 because they have the same price and in the set of Row 3 and 4, the last value is Row 4. The same logic goes for Row 9 and Row 10.

[yashwanthvijay]

I had recently attended a interview process and was asked the following qn .

Consider you have 3 different employees a, b, c and all belong to the same department 10. Their salaries are 1000, 2000 & 3000 respectively . I want the output to be displayed such as

empno

deptno

lastvalue

a

10

3000

b

10

3000

c

10

3000

.

My answer was

 

              Select empno,  deptno,   LAST_VALUE (salary)  OVER (

        PARTITION BY 

I gave this as a reply and he came back asking me to display only 2000 now in the output. I didn’t know how to get it done and would like to know your answer.

Later when I asked him the answer, He mentioned to use analytical functions. wOuld be glad to have your assistance

[satvikchauhan]

select id, name, salary, department, last_VALUE(name)over(partition by (department)order by salary desc rows between unbounded preceding and unbounded following) from practice;


plz use and between unbounded


your code without "and" is not working in sql server management studio 2019

thankyou