Returning 2nd most record in each group using SQL Server 2005

I received a problem about finding 2nd highest record for every group in a dataset. This is a little different then find top 2nd data-set. We have multiple solutions for this. I am going to discuss one solution here. We need to use dense_rank function to find its easiest solution. By using that we can rank each row according to its group and from that group we can find all records with rank number equals 2. You can find solution in following example:

I have created a declare table to insert few sample records.

We can get following output from this query

Here is the query to get 2nd highest salary of each group.

In this way we can get any level of records.