In the previous post, we explored SQL Server FIRST_VALUE Window Function with different example use cases. In this post, we will be understanding LAST_VALUE Window Function, its syntax, and examples. As the name suggests, Last_value returns the last value in an ordered result set. It's the opposite of the First_value window function. The last_value function like other window functions will apply to the result set of the query output, distributing the rows as per the grouping/partitioning conditions mentioned, and ordering each partition set as per the criteria mentioned. If no partition sub-clause is mentioned, then the function considers the whole query output as a group and applies to order criteria on it, before executing the window function.
Syntax :
LAST_VALUE ( [scalar_expression ] ) OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
scalar_expression: The value to be returned, can be a column name, subquery, or an expression of any type which returns a single value. It cannot be an analytic function.
partition_clause: it makes the partitioning or grouping of the result-set returned by the FROM clause as per the criteria mentioned in the OVER PARTITION BY sub-clause. And if not mentioned then, the function treats all result-set of the query as a single grouping set to perform the operations.
order_by: it sorts the rows inside the grouping set as per the criteria mentioned.
Let's create a table and example insert statement, to use for the demonstration below. For demonstration, we will be working on the employee's table having columns id, first_name, last_name, department_id, and joining_date for the employee. Now we wanted to find out when was the last time someone joined the company.
Another use-case of the first_value and last_value window function is to find the first time and last time of discussion made on a certain topic like a support request, wherein we might need to know when the issue was raised and when the issue was resolved and when the last time the discussion made on support chat for that issue.
CREATE TABLE employees (
id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id TINYINT,
joining_date DATE
);
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (1, 'Israel', 'Gregersen', 3, '3/6/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (2, 'Jobina', 'Fooks', 3, '3/6/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (3, 'Hamil', 'Billin', 2, '3/3/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (4, 'Rodrick', 'Pettifer', 3, '3/5/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (5, 'Lucila', 'Buessen', 2, '3/5/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (6, 'Petronilla', 'Bugdell', 3, '3/3/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (7, 'Theodoric', 'Zum Felde', 3, '3/2/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (8, 'Debbie', 'Kollaschek', 2, '3/7/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (9, 'Mavis', 'Gowdridge', 1, '3/6/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (10, 'Muffin', 'Closs', 4, '3/2/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (11, 'Nike', 'MacLese', 4, '3/8/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (12, 'Georgeta', 'Bahl', 2, '3/6/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (13, 'Danika', 'McDool', 4, '3/8/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (14, 'Theodore', 'Burchett', 1, '3/2/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (15, 'Colin', 'Gale', 3, '3/6/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (16, 'Brande', 'Danzey', 4, '3/6/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (17, 'Othelia', 'Boddis', 4, '3/4/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (18, 'Hugh', 'Pendreigh', 1, '3/4/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (19, 'Marya', 'Gabites', 1, '3/8/2021');
INSERT INTO employees (id, first_name, last_name, department_id, joining_date) VALUES (20, 'Brit', 'Tommei', 4, '3/3/2021');
SQL last_value window function over query result-set with partition example
As discussed above, we have created a table for the employees to store their information. Now, management wanted to know when was the last time a person newly joined any department.' since we wanted to know the date of the last joined for each department, let's try it with the last_value window function.
SELECT
id,
first_name,
last_name,
department_id,
joining_date,
LAST_VALUE(joining_date) OVER(partition by department_id order by joining_date asc RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) last_joined_date
FROM employees;
In the above output of the query, we returned the columns of the employee's table along with the last hired for that department as last_joined_date. Do remember that the ORDER BY sub-clause of the OVER clause is mandatory for both FIRST_VALUE and LAST_VALUE Window Functions.
SQL FIRST_VALUE and LAST_VALUE over query result-set with partition example
To demonstrate the use of both FIRST_VALUE and LAST_VALUE, let's create a table, which records the data of the messages for each conversation. The message table will contain conversation_id indicating the message is related to which conversation, it will also contain other attributes like message_id, message_content, status, and created_on. For simplicity's sake, let's keep attributes simple.
create table messages(
message_id int,
conversation_id int,
message_content text,
created_on datetime2 not null,
);
insert into messages (message_id, conversation_id, message_content, created_on) values (1, 5, 'erat curabitur gravida nisi at nibh in hac habitasse platea dictumst aliquam augue quam sollicitudin vitae consectetuer eget', '2021-03-06 06:15:10');
insert into messages (message_id, conversation_id, message_content, created_on) values (2, 6, 'rhoncus dui vel sem sed sagittis nam congue risus semper porta volutpat quam', '2021-03-03 16:25:35');
insert into messages (message_id, conversation_id, message_content, created_on) values (3, 5, 'velit vivamus vel nulla eget eros elementum pellentesque quisque porta volutpat erat quisque erat eros viverra eget', '2021-03-04 11:52:25');
insert into messages (message_id, conversation_id, message_content, created_on) values (4, 6, 'consectetuer eget rutrum at lorem integer tincidunt ante vel ipsum praesent blandit lacinia erat', '2021-03-04 14:50:29');
insert into messages (message_id, conversation_id, message_content, created_on) values (5, 3, 'vulputate justo in blandit ultrices enim lorem ipsum dolor sit amet', '2021-03-08 19:02:02');
insert into messages (message_id, conversation_id, message_content, created_on) values (6, 6, 'ipsum ac tellus semper interdum mauris ullamcorper purus sit amet nulla quisque arcu', '2021-03-04 07:16:31');
insert into messages (message_id, conversation_id, message_content, created_on) values (7, 4, 'magna vestibulum aliquet ultrices erat tortor sollicitudin mi sit amet lobortis sapien sapien', '2021-03-04 03:30:45');
insert into messages (message_id, conversation_id, message_content, created_on) values (8, 6, 'in sagittis dui vel nisl duis ac nibh fusce lacus purus aliquet at feugiat non pretium quis lectus suspendisse', '2021-03-03 18:23:46');
insert into messages (message_id, conversation_id, message_content, created_on) values (9, 6, 'vestibulum eget vulputate ut ultrices vel augue vestibulum ante ipsum primis in faucibus orci luctus', '2021-03-08 00:17:42');
insert into messages (message_id, conversation_id, message_content, created_on) values (10, 6, 'quis libero nullam sit amet turpis elementum ligula vehicula consequat morbi a ipsum integer a nibh', '2021-03-08 06:11:18');
insert into messages (message_id, conversation_id, message_content, created_on) values (11, 5, 'id ornare imperdiet sapien urna pretium nisl ut volutpat sapien arcu sed augue aliquam erat volutpat', '2021-03-03 06:42:55');
insert into messages (message_id, conversation_id, message_content, created_on) values (12, 2, 'amet lobortis sapien sapien non mi integer ac neque duis bibendum morbi', '2021-03-07 04:27:28');
insert into messages (message_id, conversation_id, message_content, created_on) values (13, 5, 'mauris laoreet ut rhoncus aliquet pulvinar sed nisl nunc rhoncus dui vel sem sed sagittis', '2021-03-07 23:54:14');
insert into messages (message_id, conversation_id, message_content, created_on) values (14, 6, 'at vulputate vitae nisl aenean lectus pellentesque eget nunc donec quis orci eget orci vehicula condimentum curabitur in libero ut', '2021-03-05 13:40:03');
insert into messages (message_id, conversation_id, message_content, created_on) values (15, 5, 'elementum ligula vehicula consequat morbi a ipsum integer a nibh in quis justo maecenas', '2021-03-06 22:45:54');
insert into messages (message_id, conversation_id, message_content, created_on) values (16, 2, 'sed vel enim sit amet nunc viverra dapibus nulla suscipit', '2021-03-04 17:12:58');
insert into messages (message_id, conversation_id, message_content, created_on) values (17, 2, 'ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia curae mauris viverra diam vitae quam', '2021-03-02 11:09:49');
insert into messages (message_id, conversation_id, message_content, created_on) values (18, 5, 'in libero ut massa volutpat convallis morbi odio odio elementum eu', '2021-03-07 04:59:52');
insert into messages (message_id, conversation_id, message_content, created_on) values (19, 3, 'velit donec diam neque vestibulum eget vulputate ut ultrices vel augue vestibulum', '2021-03-05 03:57:53');
insert into messages (message_id, conversation_id, message_content, created_on) values (20, 5, 'est quam pharetra magna ac consequat metus sapien ut nunc vestibulum ante', '2021-03-04 12:34:14');
With data inserted in the table above, let's write the query,
select
message_id,
conversation_id,
message_content,
created_on,
first_value(message_id) over(partition by conversation_id order by created_on asc) as first_message_id_of_conversation,
last_value(message_id) over(partition by conversation_id order by created_on asc RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) as last_message_id_of_conversation
from messages;
The query result set is partitioned into 5 grouping sets, as we had 5 unique conversations. For each conversation, we needed to know the starting message id with which the conversation started and the last message_id of that conversation. This is one of the classic use cases where both the FIRST_VALUE and LAST_VALUE window function is used to find the starting message and last message of the conversation. We partitioned the query result set on conversation_id and ordered it based on the created_on column for finding the first message of the conversation. To find the last message of the conversation we also partitioned the query result-set on the conversation_id column and ordered it based on the created_on column, along with the range. We had to set the range in the sub-clause to order by sub-clause because we wanted to bound the range from the current row to all following rows in the partitioned ordered set. And default bound for the range is from the preceding rows to the current row. Thus for conversation id 2, the first message of the conversation is message_id 17 and the last message of that conversation is 12. The first message of the conversation, for conversation id 5 is message_id 11 and the last message of that conversation is message_id 13. The same follows for all the conversations.
Summary
The LAST_VALUE function returns the last value in the partitioned ordered query result set.
PARTITION BY sub-clause sets the local boundary of the data set as per the mentioned specified conditions. If not mentioned, it considers the whole result sets for the boundary.
ORDER BY sub-clause of the OVER clause is required.