# Sorting BIGINT column with ascending positive values followed by descending nonpositive values

I have a column flowing in with the full range of integers. Whilst generating keys on the INT column, I need to push the 0's and negatives down (ordered DESC) and the positive keys to stay at the top (ordered ASC/DESC). I came up with two approaches but I am not sure which is better.

Approach 1: Tidier but unsure about scalability

WITH t1(c1) AS
(
SELECT 11 UNION ALL
SELECT 2 UNION ALL
SELECT 0 UNION ALL
SELECT -1 UNION ALL
SELECT -11
)
SELECT c1, ROW_NUMBER() OVER (ORDER BY
CASE WHEN c1 <=0 THEN c1 END DESC ,
CASE WHEN c1 >0 THEN c1 END ASC) keys
FROM t1 ;


Approach 2: Scalable

WITH t1(c1) AS
(
SELECT 11 UNION ALL
SELECT 2 UNION ALL
SELECT 0 UNION ALL
SELECT -1 UNION ALL
SELECT -11
)
SELECT c1, ROW_NUMBER() OVER (ORDER BY c1 ASC) keys
FROM t1
WHERE c1 >0
UNION ALL
-- Need to include Max keys from previous step in actual implementation
SELECT c1,
maxkeys + ROW_NUMBER() OVER (ORDER BY c1 DESC) keys
FROM t1
WHERE c1 <= 0 ;


Please suggest any other better alternatives for scalability (BIGINT range).

• The approach #2 is broken. Please fix. Also --- it's not completely clear from the description what your intention is. Testing your approach #1, I get roughly same ordering as if I had applied Abs() on the values, but it sounds like you want positive to come before the negative values? – this Dec 15 '17 at 2:29