Departmentwise 2nd highest salary

In SQL Server 2005/2008 you can use something like this:

;WITH myCTE (Position, myEmployee, myDepartment, mySalary)
AS
(
    SELECT 
     ROW_NUMBER() OVER(PARTITION BY myDepartment ORDER BY mySalary DESC), 
     myEmployee, 
     myDepartment, 
     mySalary 
    FROM myEmployees
)

SELECT 
    mySalary, 
    myEmployee, 
    myDepartment 
FROM myCTE 
WHERE Position=2

You have to modify and test the code to match your database. I haven't done any testing, but it should work. If performance is an issue, I think there are other solutions that are faster, but I leave that up to you to compare different solutions.
If you want to return all employees that has the same salary (2:nd highest) you should use DENSE_RANK instead of ROW_NUMBER. Credits to Pavel, add a vote for his answer!

;WITH myCTE (Position, myEmployee, myDepartment, mySalary)
AS
(
    SELECT 
     DENSE_RANK() OVER(PARTITION BY myDepartment ORDER BY mySalary DESC), 
     myEmployee, 
     myDepartment, 
     mySalary 
    FROM myEmployees
)

SELECT 
    mySalary, 
    myEmployee, 
    myDepartment 
FROM myCTE 
WHERE Position=2