I am trying to add a sequence order to my raw data and was wondering if there is an efficient way to do this without a while exists loop as I have more than million records to order.
Example dataset:
CustomerID StartDate EndDate EnrollID
-------------------------------------------
1 1/1/1990 1/1/1991 14994
2 1/1/1990 1/1/1992 14995
2 1/1/1993 1/1/1995 14997
1 1/1/1992 1/1/1993 14996
1 1/1/1993 1/1/1994 14997
2 1/1/1995 1/1/1996 14998
3 1/1/1990 1/1/1991 15000
3 1/1/1992 1/1/1993 15001
3 1/1/1995 1/1/1996 15007
Re-ordered data should add a sequence/ order for each customer id based on min(startdate), min(enddate) , min(enrollid)
Final output Dataset should look like below where each customerID records are ordered by min(StartDate), min(EndDate), min(EnrollID)
CustomerID StartDate EndDate EnrollID Sequence_Order
----------------------------------------------------------
1 1/1/1990 1/1/1991 14994 1
1 1/1/1992 1/1/1993 14996 2
1 1/1/1993 1/1/1994 14997 3
2 1/1/1990 1/1/1992 14995 1
2 1/1/1993 1/1/1995 14997 2
2 1/1/1995 1/1/1996 14998 3
3 1/1/1990 1/1/1991 15000 1
3 1/1/1992 1/1/1993 15001 2
3 1/1/1995 1/1/1996 15007 3
Need the fastest way to do this in T-SQL
Use ROW_NUMBER()
SELECT CustomerID, StartDate, EndDate, EnrollID,
ROW_NUMBER() OVER(
PARTITION BY CustomerId
ORDER BY StartDate
,EndDate
,EnrollID
) AS Sequence_Order
FROM Table1
OUTPUT:
CustomerID StartDate EndDate EnrollID Sequence_Order
1 1990-01-01 1991-01-01 14994 1
1 1992-01-01 1993-01-01 14996 2
1 1993-01-01 1994-01-01 14997 3
2 1990-01-01 1992-01-01 14995 1
2 1993-01-01 1995-01-01 14997 2
2 1995-01-01 1996-01-01 14998 3
3 1990-01-01 1991-01-01 15000 1
3 1992-01-01 1993-01-01 15001 2
3 1995-01-01 1996-01-01 15007 3
Follow the link to the demo: