How can I add a sequence / order a data in SQL

Question

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

Can you do it in batches or does it need to happen all in one go? What happens when a new record is inserted? Can you add indexes? What indexes exist? — Nick.Mc
– Nick.Mc, Commented Apr 23, 2018 at 4:38
you can do it in your SELECT statement using row_number() — Squirrel
– Squirrel, Commented Apr 23, 2018 at 4:40

Nishant Gupta · Accepted Answer · 2018-04-23 04:43:48Z

1

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:

http://sqlfiddle.com/#!18/dbe66/2

answered Apr 23, 2018 at 4:43

Nishant Gupta

3,6761 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SNW Over a year ago

This works! THANK YOU Nishant! Appreciate your help!

uzi · Accepted Answer · 2018-04-23 04:40:23Z

1

Use Row_number

select
    *, row_number() over (partition by CustomerID order by StartDate, EndDate, EnrollID) as Sequence_Order
from myTable

answered Apr 23, 2018 at 4:40

uzi

4,1561 gold badge18 silver badges22 bronze badges

Comments

Faraz Babakhel · Accepted Answer · 2018-04-23 04:45:35Z

0

The Order= ROW_NUMBER() OVER (ORDER BY column) is uses for. try the below answer

SELECT  (Order= ROW_NUMBER() OVER ( ORDER BY CustomerID)) as Sequence ,
    CustomerID,
     StartDate,
     EndDate,
     EnrollID
     FROM    dbo.SomeTable

Reference

edited Apr 23, 2018 at 4:45

answered Apr 23, 2018 at 4:40

Faraz Babakhel

6645 silver badges17 bronze badges

2 Comments

Faraz Babakhel Over a year ago

@SNW try this answer

SNW Over a year ago

Thank you so much for your response. I appreciate it. This needs partition by and order by to get the sequence for each customerID.

Rahul Jain · Accepted Answer · 2018-04-23 05:12:07Z

0

Try this:

SELECT customerId, startDate, endDate, enrollID,
ROW_NUMBER() OVER(
     PARTITION BY customerId 
     ORDER BY startDate
              ,endDate 
              ,enrollID
) AS seq
FROM Table1
ORDER BY customerId 
     ,startDate
     ,endDate 
     ,enrollID

Order by is required in the end to sort the final output

answered Apr 23, 2018 at 5:12

Rahul Jain

1,3997 silver badges16 bronze badges

2 Comments

SNW Over a year ago

This works perfect. Partition by and Order by are both needed to get the right row number but just wondering why do we need the second order by outside too? I tried it without the second order by and it works perfect. Just wondering if i am missing something. Thank you again for your response. I appreciate it.

Rahul Jain Over a year ago

Order by is not compulsory. Just added to sort the final result. You can skip it if you want.

Collectives™ on Stack Overflow

How can I add a sequence / order a data in SQL

4 Answers 4

1 Comment

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related