There is no ROWNUM pseudo column in Redshift. If you are coming from Oracle database background, you will find it difficult in Redshift without ROWNUM pseudo column. The one possible solution to this is ROW_NUMBER() analytical function as Redshift ROWNUM pseudo column alternative.
ROWNUM is sometime useful when you are working with multi-level SQL queries. You can restrict the rows using ROW_NUMBER functions.
Read:
- Redshift String Functions and Examples
- Amazon Redshift Date Functions and Examples
- Redshift Analytics Functions and Examples
Redshift also support a LIMIT clause to restrict the output. You can even create identity column in the Redshift table.
ROW_NUMBER as a Redshift ROWNUM Pseudo Column Alternative
You can use the ROW_NUMBER analytical function to produce the sequential row numbers. The number generated are based on the partition and order by column. In the below example we are generating row numbers based on ‘code’ column and we have not mentioned any partition column as we want consider all data in the table as single partition.
For Example;
https://gist.github.com/500de45a3d9a10ec551cc45259c42142
You should not provide any PARTITION BY clause here as you will be considering all records as single partition for ROW_NUMBER function.
The above solution will work perfectly but the only issue is ROWNUM order is determined by the ORDER BY clause provide in OVER clause.
Feel free to suggest any other method that you are thinking or using to generate row number in Redshift 🙂
Related Articles,
you can also try SELECT ROW_NUMBER() OVER( ORDER BY 2) as ROWNUM from table_name limit 10;