CODEWITHSUNDEEP
sqlnetezzaaginity
user15957051
user15957051

Reputation: 3

generate serial number in decreasing order given a variable in netezza aginity sql

Is there any SQL syntax using netezza SQL, given column number, trying to generate rows for number in decreasing order down to 0.

Below is an example of what I'm trying to do

BEFORE

ID NUMBER
A 4
B 5

AFTER

ID NUMBER
A 4
A 3
A 2
A 1
B 5
B 4
B 3
B 2
B 1

please also click to see screenshot for example thanks

Upvotes: 0

Views: 136

Answers (1)

Aniket Kulkarni
Aniket Kulkarni

Reputation: 471

You can use the _v_vector_idx table for this purpose

select 
  id, idx 
from 
  test join _v_vector_idx 
     on idx <= number
order
  by id asc, idx desc ;

Here's the example in action

select * from test
  ID   | NUMBER
-------+--------
 A     |      4
 B     |      5
(2 rows)


select id, idx from test join _v_vector_idx on
   idx <= number order by id asc, idx desc ;
  ID   | IDX
-------+-----
 A     |   4
 A     |   3
 A     |   2
 A     |   1
 A     |   0
 B     |   5
 B     |   4
 B     |   3
 B     |   2
 B     |   1
 B     |   0
(11 rows)

insert into test values ('C', 3);
INSERT 0 1

select * from test;
  ID   | NUMBER
-------+--------
 A     |      4
 B     |      5
 C     |      3
(3 rows)

select id, idx from test join _v_vector_idx
  on idx <= number order by id asc, idx desc ;
  ID   | IDX
-------+-----
 A     |   4
 A     |   3
 A     |   2
 A     |   1
 A     |   0
 B     |   5
 B     |   4
 B     |   3
 B     |   2
 B     |   1
 B     |   0
 C     |   3
 C     |   2
 C     |   1
 C     |   0
(15 rows)

Upvotes: 1

Related Questions