Order max and min value rows alternatively in SQL Server

In this blog post “Order max and min value rows alternatively in SQL Server”, we are going to learn how we can order the rows of a table by combining max and min value rows alternatively. For example, from an employee table based on the salary column, we need to extract the records ordered as;

First row: First highest salary

Second row: First lowest salary

Third row: Second highest salary

Fourth row: Second lowest salary

Fifth row: Third highest salary

Sixth row: Third lowest salary

and so on…….

We know that the SQL Server database engine is optimized to operate in a set oriented way instead of RBAR (Row By Agonizing Row, processing row by row in a loop). We are going to achieve the desired output in a set based approach, unlike loops or recursive CTEs. Let’s have this tutorial in action now.

Create the dummy employee table and insert few records in it. Here is the script:

--Create dummy employee table
CREATE TABLE tbl_Employee
(
 Id INT,
 Name VARCHAR(100),
 Salary NUMERIC(9, 2)
)
GO

--Insert few dummy rows in the table
INSERT INTO #Employee
(Id, Name, Salary)
VALUES(100, 'John', 7000),
(101, 'Scott', 30000),
(102, 'Jeff', 2000),
(103, 'Jimy', 10000),
(104, 'Andrew', 500),
(105, 'Alister', 100)
GO

Have a look on the data available in the employee table:

SELECT * FROM tbl_Employee

Desired output

The desired output from the above table is as;

How to order max and min value rows alternatively in SQL Server

We are going to achieve the desired output in two ways.

Method 1: Using UNION ALL

DECLARE @Cnt INT = 0, @SeqLimit INT = 0
SELECT @Cnt = COUNT(1) FROM tbl_employee
SET @SeqLimit = CEILING(@Cnt / 2.0)

SELECT * FROM 
(
 SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS SEQ, Id, Name, Salary FROM tbl_employee
)DT1
WHERE SEQ <= @SeqLimit
UNION ALL
SELECT * FROM
(
 SELECT ROW_NUMBER() OVER(ORDER BY Salary ASC) AS SEQ, Id, Name, Salary FROM tbl_employee
)DT2
WHERE SEQ <= @SeqLimit - (@Cnt % 2)
ORDER BY SEQ ASC, Salary DESC

In this method, we have divided the table in two parts, half with highest salaries and another half with lowest salaries, and ordered the rows with a sequence in descending and ascending order to arrange the records in highest to lowest and lowest to highest based on salary. Finally, we have applied an UNION ALL on both result sets and orders them with Seq (ascending) and Salary (descending) to get the desired output. To handle the total number of rows in the table which could be even or odd, we have used “SET @SeqLimit = CEILING(@Cnt / 2.0)“.

Method 2: Using INNER JOIN

In this approach, we are going to use a serial number table to achieve the desired output. Script to create and insert data in serial number table is as below:

CREATE TABLE SerialNumber
(
 RowId INT NOT NULL PRIMARY KEY
)

INSERT INTO SerialNumber (RowId)
SELECT TOP 100000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS SEQ 
FROM SYS.COLUMNS SC1
CROSS JOIN SYS.COLUMNS SC2

You can use your own method to insert the data in serial number table.

Below is the query to fetch the desired output using an INNER JOIN:

DECLARE @Cnt INT = (SELECT COUNT(1) FROM tbl_employee)
SELECT * FROM SerialNumber SN
INNER JOIN 
(SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS Rnk, * FROM tbl_employee) EMP 
ON SN.RowId = EMP.Rnk OR ((EMP.Rnk = (@Cnt - SN.RowId + 1)))
WHERE RowId <= CEILING(@Cnt / 2.0)
ORDER BY SN.RowId

This approach uses an inner join technique to map a single row from serial number table to two rows of employee table; one with highest salary “SN.RowId = EMP.Rnk” and other is the lowest salary “((EMP.Rnk = (@Cnt – SN.RowId + 1)))“.

Thanks for the reading. Please do share and rate us and give us your valuable inputs in comment section of this post.

Connect with us on social media to be updated:
Facebook || Google plus || Twitter