CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
Narek Nersisyan
Narek Nersisyan

Reputation: 75

How to make a table of consecutive integers in Amazon Redshift SQL?

I would like to create a table in Amazon Redshift SQL or in PostgreSQL that will contain consecutive integers from 0 to some large random number. This should be done without using arrays which are not supported in Redshift version of SQL.

Upvotes: 0

Views: 304

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

CREATE TEMP TABLE tmp_numbers_1000 AS 
SELECT ROW_NUMBER() OVER() 
FROM stl_scan 
LIMIT 1000;

SELECT * FROM tmp_numbers_1000 ORDER BY 1 LIMIT 10;
 row_number
------------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
(10 rows)

SELECT * FROM tmp_numbers_1000 ORDER BY 1 DESC LIMIT 10;
 row_number
------------
       1000
        999
        998
        997
        996
        995
        994
        993
        992
        991
(10 rows)

Upvotes: 1

Related Questions