Sorting and Flagging the data

Question

I have a huge dataset lies in Amazon Redshift server and i need to do some sorting and flagging to the dataset (attached sample dataset for reference). Two tasks I need to achieve:

1. I need to sort the data by S_Number and days_difference in Ascending order

2. Flagging - Need to create a new column called flag and it should do the following steps:

   • If my row 1: S_Number = row -1: S_Number then row -1: Flag +1 else 1.

I am using Alteryx for pulling the data, but still I use SQL query for achieving the task. Any help would be really helpful.

I have given the links to the sample input file as well as expected output. Let me know my question is not clear.

Sample_Input_File

Expected_Output

Answer 1

If I understood properly, you want to sort the results and add the position of each row grouped by S_Number. If so, that is called a "window function" in SQL and in Redshift, and you can get results like the ones you shared with the query below. Please note I didn't run the query so it might have a small syntax error (hopefully not).

SELECT timestamp, S_Number, days_difference, ROW_NUMBER () OVER
(PARTITION BY S_Number ORDER BY timestamp ASC) AS Flag
FROM your_table
ORDER BY S_Number ASC, timestamp ASC