CODEWITHSUNDEEP
sqldatabaset-sqlsql-server-2014
SNW
SNW

Reputation: 13

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

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

Upvotes: 1

Views: 305

Answers (4)

Rahul Jain
Rahul Jain

Reputation: 1399

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

Upvotes: 0

Faraz Babakhel
Faraz Babakhel

Reputation: 664

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

Upvotes: 0

Nishant Gupta
Nishant Gupta

Reputation: 3656

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

Upvotes: 1

uzi
uzi

Reputation: 4146

Use Row_number

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

Upvotes: 1

Related Questions