How to efficiently write keying logic for full range of BIGINT

Question

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 the best

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

I don't see any scalability issues with 1st approach. It was quite hard to read. I would suggest to use "ROW_NUMBER() OVER (ORDER BY sign(c1) desc, abs(c1))" for shorter code and better readability. — Edgars T., Dec 13 '17 at 15:01
@EdgarsT. I agree on the shortened form. But they are not clearly interchangeable to guarantee the same keys since always SIGN should come first and then ABS, unlike my original solution. Also if I need to do both a DESC ordering and still would like to keep zeros and negatives in the bottom, then the negative keys will be sorted differently. — Srini V, Dec 13 '17 at 15:08
Using added example data, my logic and your approach 1 logic provided same result. Can you provide some example where these results are different? — Edgars T., Dec 13 '17 at 15:13
OK. Here you go... `ROW_NUMBER() OVER (ORDER BY SIGN(c1) DESC ,ABS(c1) )` != `ROW_NUMBER() OVER (ORDER BY ABS(c1) , SIGN(c1) DESC ,)` whereas `ROW_NUMBER() OVER (ORDER BY CASE WHEN c1 <=0 THEN c1 END DESC , CASE WHEN c1 >0 THEN c1 END ASC)` = `ROW_NUMBER() OVER (ORDER BY CASE WHEN c1 >0 THEN c1 END ASC, CASE WHEN c1 <=0 THEN c1 END DESC)` — Srini V, Dec 13 '17 at 15:54
What is use case for order statement to be interchangeable? If you want to do both a DESC ordering and still would like to keep zeros and negatives in the bottom, you can use just plain c1 column: OVER (ORDER BY c1 DESC) — Edgars T., Dec 13 '17 at 16:13
Fair point. But this is the output of dynamic SQL. Hence wanted to be sure. — Srini V, Dec 13 '17 at 17:53

score 0 · Accepted Answer · answered Dec 20 '17 at 11:23

0

After a round of performance testing, we found option 1 was more elegant and optimum solution in this case. Also, this snippet is getting generated for numerous tables dynamically, it was easier to configure and turn on and off.

answered Dec 20 '17 at 11:23

Srini V

11,045
14
66
89

How to efficiently write keying logic for full range of BIGINT

1 Answers1