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 easy we can get result this is one of the challenge for us.

Solution:-

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

SQL SERVER 2012 provides you 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 see 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 frist 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 !!!

RJ

Ranking by Rank() function then what about Row_NUBER() TIP #37

 

In last post we have discussed Row_Number() function. In similar way we have RANK function. By the name it is clear that we provide  some kind of ranking for rows.

It syntax is exact same like ROW_NUMBER().

Now the question comes in mind if it is same then why SQL SERVER introduce new function.

so there is slightly difference

ROW_NUMBER() will generate sequential number regardless of  duplicate rows in partition.

While  RANK() will generate sequential number for unique row in partition and use same sequence for duplicate and skip sequence which lies between duplicate.

For example in  below query we want to know total dues rank according to  territory and  modified date.

so we write following

SELECT TerritoryID,TotalDue,ModifiedDate,SalesOrderNumber ,RANK() OVER (Partition by ModifiedDate,TerritoryID ORDER BY TotalDue) ranks FROM [Sales].[SalesOrderHeader]

When we run it we get following result

enjoy !!!

RJ

Row_Number() function for providing sequence number as per your wish TIP #35

 

Sometimes we require sequence column or we can a row number column so we have ROW_NUMBER() function in SQL SERVER.

Lets understand this by following example

In this example we have  person table which have firstname, last name, middlename  columns. Now we want to fetch records  with a extra column ROW_Number() so we write following query

SELECT ROW_NUMBER() OVER (ORDER BY LastName) As ROWNO,FirstName,MiddleName,LastName  FROM [Person].[Person]

When we run above query we get following result set. If you see below result set we have one extra column which is ROWNO

Lets understand the query in this we are getting sequence or Row number order by Last name.

There is one more option with ROW_NUMBER which is partition by

lets understand this by below query what we want for we want row number for each record based on last name means whenever new last name introduce row number start again from one. to achieve this we have to write following query

SELECT ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY LastName) As rowNo,FirstName,MiddleName,LastName  FROM [Person].[Person]

See below snap when we run we get following result

If you see above snap you will find that when the Last name change row number also change so we did partition by last name means for each change.

I hope you might have used earlier but I thought It might be possible it will helpful to someone who not aware.

Enjoy!!

RJ