Maximum and Minimum Rows Alternatively in SQL Server

Question

This is an Employee table,

Id    Name     Salary
1       A.J     7000
2       B.S     30000
3       C.K     2000
4       D.O     10000
5       E.L     500

Now i want to display 1st highest salary then minimum salary then 2nd maximum salary then 2nd minimum salaray and so on..up to nth row.

Expected Output,

Id     Name    Salary
2       B.S     30000
5       E.L     500
4       D.O     10000
3       C.K     2000
1       A.J     7000

Answer 1

One more variant without explicit COUNT. SQL Fiddle.

Try also to add this row to sample data (6, 'X.Y', 7000) in the fiddle. The query still returns correct results.

DECLARE @Employee TABLE (ID int, Name nvarchar(50), Salary money);

INSERT INTO @Employee (ID, Name, Salary) VALUES
(1, 'A.J', 7000),
(2, 'B.S', 30000),
(3, 'C.K', 2000),
(4, 'D.O', 10000),
(5, 'E.L', 500);

WITH
CTE
AS
(
    SELECT *, NTILE(2) OVER (ORDER BY Salary, ID) AS n
    FROM @Employee AS E
)
SELECT
    *
    ,SIGN(n-1.5) AS s
    ,SIGN(n-1.5)*Salary AS ss
    ,ROW_NUMBER() OVER(PARTITION BY n ORDER BY SIGN(n-1.5)*Salary DESC) AS rn
FROM CTE
ORDER BY rn, ss DESC;

Result

ID    Name    Salary      n       s       ss          rn
2     B.S     30000.00    2     1.0    30000.00000    1
5     E.L     500.00      1    -1.0     -500.00000    1
4     D.O     10000.00    2     1.0    10000.00000    2
3     C.K     2000.00     1    -1.0    -2000.00000    2
1     A.J     7000.00     1    -1.0    -7000.00000    3

I left intermediary columns in the output to illustrate how it works.