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
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.