Is there a way to reset row_number() to 1 once it reaches a distinct value?

edit

Yes, you can reset row_number() to 1 once it reaches a distinct value by using the PARTITION BY clause.

For example, consider the following table named "sales":

| Sale_id | Date | Product | Revenue | |--------|------------|---------|---------| | 1 | 2022-01-01 | A | 100 | | 2 | 2022-01-02 | B | 150 | | 3 | 2022-01-02 | A | 200 | | 4 | 2022-01-03 | A | 300 | | 5 | 2022-01-03 | B | 250 |

To reset row_number() to 1 once it reaches a distinct value of "Date", you can use the following SQL query:

SELECT 
  ROW_NUMBER() OVER (PARTITION BY Date ORDER BY Sale_id) AS row_number,
  Sale_id,
  Date,
  Product,
  Revenue
FROM sales;

The result would be:

| rownumber | Saleid | Date | Product | Revenue | |------------|--------|------------|---------|---------| | 1 | 1 | 2022-01-01 | A | 100 | | 1 | 2 | 2022-01-02 | B | 150 | | 2 | 3 | 2022-01-02 | A | 200 | | 1 | 4 | 2022-01-03 | A | 300 | | 2 | 5 | 2022-01-03 | B | 250 |

As you can see, the row_number() resets to 1 whenever the value of "Date" changes.