Solved

How to order Highest and lowest value rows alternatively in SQL Server ?

Posted on 2016-11-18
4
24 Views
Last Modified: 2016-11-18
Sample data-

CREATE TABLE #emp_records
(
 Id INT,
 Name VARCHAR(80),
 Salary NUMERIC(8, 2)
)
GO
 
INSERT INTO #emp_records
(Id, Name, Salary)
VALUES(1, 'A', 7000),
(2, 'B', 30000),
(3, 'C', 6000),
(4, 'D', 10000),
(5, 'E', 800),
(6, 'F', 500)

Desired Output :-

Id      Name      Salary
2      B      30000.00
6      F      500.00
4      D      10000.00
5      E      800.00
1      A      7000.00
3      C      6000.00
0
Comment
Question by:Mayank Tripathi
  • 2
  • 2
4 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41892727
Do you always have even number in this case ? or we can have odd number also ?
0
 

Author Comment

by:Mayank Tripathi
ID: 41892728
Thanks Pawan, by the way the rows are not fixed.
0
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
ID: 41892731
try..

---

DECLARE @Count INT = 0
SELECT @Count = COUNT(1) FROM emp_records
DECLARE @limit INT = CEILING(@Count / 2.0)

;WITH CTE AS
(
	SELECT * FROM
	(
		SELECT * , ROW_NUMBER() OVER(ORDER BY Salary DESC) rnk FROM emp_records
	)B1 
	WHERE rnk <= @limit
)
,CTE1 AS 
(
	SELECT * FROM
	(
		SELECT *, ROW_NUMBER() OVER(ORDER BY Salary ASC) rnk FROM emp_records
	)B2
	WHERE rnk <= @limit - (@Count % 2.0) 
)
SELECT * FROM CTE c
UNION ALL
SELECT * FROM CTE1
ORDER BY rnk ASC, Salary DESC

Open in new window


Hope it helps !
0
 

Author Closing Comment

by:Mayank Tripathi
ID: 41892793
Thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 21
Analysis of table use 7 27
encyps queries mssql 15 26
SQL Split character from numbers 3 16
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now