Microsoft Business Intelligence: First

Saturday 20 July 2024

First_value in SQL server

The First_value function returns value from the first row of the window. This allow a query to access value from multiple rows at once without doing self-join. In this function over clause with order by is mandatory to have an ordered result set.

Syntax

First_value (Column Name) OVER ([partition_by_clause col1, col2,.] ORDER BY Col1, Col2, ..)

partition_by_clause divides the rows of the result sets into partitions to which the function applies. It is an optional.

order_by_clause specify the order in which the operation is performed. it is required.

Let’s see the example

We have a table.

create table employee(

id int identity(1,1) primary key,

empid int,

emp_name varchar(50),

salary_withdraw decimal(18,10),

withdraw_dt date

)

Inserting some records in this table.

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1002,'01-31-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1000,'02-29-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1020,'03-31-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1050,'04-30-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1030,'05-31-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2002,'02-29-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2009,'03-31-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2000,'04-30-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',1999,'05-31-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1012,'02-29-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1052,'03-31-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1082,'04-30-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1002,'05-31-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',87,'02-29-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',77,'03-31-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',97,'04-30-2024');

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',67,'05-31-2024');

See the records in the table.

Basic use of FIRST_VALUE Function

We want to get the highest and lowest salary of the employ.

select empid,emp_name,salary_withdraw,withdraw_dt, FIRST_VALUE(salary_withdraw) over(order by salary_withdraw) as lowest_salary,

FIRST_VALUE(salary_withdraw) over(order by salary_withdraw desc) as highest_salary

from employee

FIRST_VALUE function with PARTITION clause

In the above example we have seen that highest and lowest salary in the origination level. If we want to get the information bout employee level I means if we want to get the highest and lowest salary withdraw of the every employee then we need to use partition by clause. For example