First_value & Last_Value According to Group Set is that Easy? Tip# 44

Problem

Most of the time, we require data in which we require first value and last value from different group of rows. Now how easily we can get results, this is one of the challenges for us.

Solution

Let's understand this by an example. Suppose you have a sales table in which you maintain daily sales. Now you want a result set in which you know what is the first sale of the day and what is the last sale of the day.

SQL SERVER 2012 provides you the facility to achieve this task easily with First_Value & Last_Value function.

The syntax of first_value & Last value is exactly same as Row_Number, Dense_Rank & Rank_function.

See below example in which I have used Adventureworks SalesOrderHeader table.

Now if you take a look at the below snap, I took a random date 2005-07-12 and fetched record and highlighted is first row & last row.

So on date 2005-07-2012 Sales order have 3953.9884 as a first value and 772.5036 as a second value.

Now above specific result, we can achieve by first_Value & Last_Value function of SQL SERVER 2012 as shown below:

So, in this way, you can achieve the first_Value & last_Value from a group of rows.

I hope this may help you somewhere.

Enjoy!!!

Filed under: CodeProject, DENALI, SQL server, SQL SERVER 2014, TIPS
Tagged: First_Value, Grouping, Last_Value