SQL Server 2008 Problem Find the Difference

ID   Name  Salary
-----------------
1    A     5000
2    B     2000
3    C     7000
4    D     6000

Now Problem is that we have to find the maximum salary and then find out the diff with other salaries.

In a Single Query.

Similarly we have to find with the 2nd and 3rd in the next query or in the similar query.

Since I'm not sure this is homework or not, I don't want to just paste a query.

Here's some tips.

There is a MAX() function in sql that you could use to find the maximum value of a particular field.

You could then use that in other calculations, even in the same query.

Use:

    SELECT x.id, x.name,
           x.salary,
           y.max_salary - x.salary AS diff
      FROM YOUR_TABLE x
CROSS JOIN (SELECT MAX(t.salary) AS max_salary
              FROM YOUR_TABLE t) y

To get the 2nd/3rd/etc highest salary, use:

    SELECT x.id, x.name,
           x.salary,
           y.max_salary - x.salary AS diff
      FROM YOUR_TABLE x
CROSS JOIN (SELECT u.max_salary 
              FROM (SELECT t.salary AS max_salary,
                           ROW_NUMBER() OVER (ORDER BY t.salary DESC) AS rank
                      FROM YOUR_TABLE t) u
             WHERE u.rank = ?) y

Replace ? with the position/rank you want to retrieve.

When finding the difference between the max, use

SELECT ID, Name, Salary-(SELECT MAX(Salary) FROM Salaries) AS SalaryDiff
FROM Salaries

To compare with the 2nd, 3rd etc.. highest salary (change =2 to =3, =4 etc. as needed.)

SELECT ID, Name, Salary-(SELECT Salary FROM (SELECT Distinct Salary from Salaries) 
      WHERE (ROW_NUMBER() OVER (ORDER BY Salary))=2) AS SalaryDiff
FROM Salaries