1
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ 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? \$\endgroup\$ – this Dec 15 '17 at 2:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.