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