Skip to content
Advertisement

I need a Row_number over partition to achieve this in SQL Server

I have three columns PID, AppNo and ProcessedDate I need a query to update the AppNo in the format below

For now the AppNo column is null.

This is the sql that is not working

Advertisement

Answer

You seem to be looking to update your original table. You can use ROW_NUMBER() in a CTE to rank records with groups having the same date (without time) and pid, ordered by date (with time) and then do the update on the fly:

Demo on DB Fiddle

Original data:

PID | AppNo | ProcessedDate   
--: | ----: | :---------------
 11 |  null | 09/30/2019 18:21
  3 |  null | 09/25/2019 08:37
  3 |  null | 09/25/2019 08:37
 11 |  null | 09/25/2019 08:39
 11 |  null | 09/25/2019 08:40
  7 |  null | 09/26/2019 14:19
  7 |  null | 09/26/2019 14:20
  7 |  null | 09/26/2019 14:22
  2 |  null | 09/26/2019 14:23
 11 |  null | 09/26/2019 14:23
 11 |  null | 09/26/2019 14:24
 11 |  null | 09/26/2019 14:24
  3 |  null | 09/26/2019 14:24

After running the query:

PID | AppNo | ProcessedDate   
--: | ----: | :---------------
 11 |     1 | 09/30/2019 18:21
  3 |     1 | 09/25/2019 08:37
  3 |     2 | 09/25/2019 08:37
 11 |     1 | 09/25/2019 08:39
 11 |     2 | 09/25/2019 08:40
  7 |     1 | 09/26/2019 14:19
  7 |     2 | 09/26/2019 14:20
  7 |     3 | 09/26/2019 14:22
  2 |     1 | 09/26/2019 14:23
 11 |     1 | 09/26/2019 14:23
 11 |     2 | 09/26/2019 14:24
 11 |     3 | 09/26/2019 14:24
  3 |     1 | 09/26/2019 14:24
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement