select customer_id, row_number()over(partition by customer id order by date) as rn from table How to get same rn when Item Id is the same? Below did not work: #1 select customer_id, row_number()over(partition by customer id, Item Id order by date) as rn from table Answer We can try to use DENSE_RANK instead of row_number window function If the optional PARTITION
Tag: amazon-redshift
How can I make TO_DATE in Amazon Redshift work with multiple date formats in the same column?
I have a string column with date-time values that I need to convert into dates in order to calculate a dense rank. This column can have any of the below 3 formats: My SQL Statement to calculate the dense rank is based on this date-time column that I need to convert to date in order to rank descending: However, I’m
Why am I unable to select all records in one table that don’t exist in another table?
I have two tables like: Desired output: I want to select all records in col1 of table_1 that aren’t in col1 of table_2. In actuality, table_1 has 65000+ rows and table_2 has around 2000 rows. My query: This returns no records. However, the reverse works as intended when I select all records in table_2 that don’t exist in table_1. I
Efficient way to join multiple columns to the same column? – SQL
I have a bunch of tables combined together, which each have a column containing some form of user_id. This leads to 12 user_id columns in total. I want to join each of these user_id columns with a user_id column in a mapping table in order to retrieve the username for each of these user ids. So (assuming I have 5
SQL, extract everything before 5th comma
For example, my column “tags” have I’m trying to return everything before 5th comma. below is the result example I’ve tried below code but it’s not working. Answer You can use See the regex demo. The REGEXP_REPLACE will find the occurrence of the following pattern: ^ – start of string (([^,]*,){4}[^,]*) – Group 1 (1 refers to this part of
Redshift table access history
I wonder are there any way to get table access history in Redshift cluster? Our cluster has a lot of tables and it is costing us a lot. I would like to discover what specific tables have not been accessed for a given period and then I would drop those tables. Are there any ways to get table access history?
Pivot and Sum in Amazon Redshift
I have a following tables table1 table2 first I’d like to join table2 as follows and then join table2 with id and pivot and sum up them I guess it is very complicated, are there any way to achieve this? In Redshift it seems that there is no way to pivot them. Thanks. Answer This looks to be a pivot
How to add SUPER column to existing AWS Redshift table?
GOAL I would like to add a Redshift SUPER column to and existing redshift table. I need this to store JSON data there CODE This is how Normally I would add a new column. 1. Tried Error running query: ALTER TABLE ADD COLUMN defined as NOT NULL must have a non-null default expression Reviewed Solutions Alter column data type in
Why does strpos, position, charindex return 1 when empty substring is passed
Why do all three of these return a value of 1 in redshift? select strpos(‘X’, ”) select position(” in ‘X’) select charindex(”, ‘X’) Answer Because the empty string is the very first part of all strings.
How can I perform the same query on multiple tables in Redshift
I’m working in SQL Workbench in Redshift. We have daily event tables for customer accounts, the same format each day just with updated info. There are currently 300+ tables. For a simple example, I would like to extract the top 10 rows from each table and place them in 1 table. Table name format is Events_001, Events_002, etc. Typical values