Select Statement Create Sequence Variable

Question

How do I create a column that ranks each ID by the First and Last Date?

ID  First_Date  Last_Date   Age_Days
A   8/28/2020   12/22/2020  116
A   1/9/2019    8/12/2019   215
A   8/15/2019   8/28/2020   379
B   5/23/2017   1/9/2019    596
B   9/21/2019   3/16/2020   177
C   3/18/2020   9/29/2020   195

Desired Result:

ID  Initial_Date    Last_Date   Age_Days    New_Column
A   1/9/2019    8/12/2019       215        1
A   8/15/2019   8/28/2020       379        2
A   8/28/2020   12/22/2020      116        3
B   5/23/2017   1/9/2019        596        1
B   9/21/2019   3/16/2020       177        2
C   3/18/2020   9/29/2020       195        1

My example SQL Server query:

SELECT 
ID, First_Date, Last_Date, Age_Days
FROM Table
ORDER BY ID, First_Date, Last_Date

DENSE_RANK

astentx
– astentx

2021-02-25 14:41:07 +00:00
Commented Feb 25, 2021 at 14:41 — astentx
– astentx, Commented Feb 25, 2021 at 14:41

Gordon Linoff · Accepted Answer · 2021-02-25 14:44:25Z

2

I think you just want row_number():

select t.*,
       row_number() over (partition by id order by first_date) as new_column
from t
order by id, first_date;

Your sample data has no ties on the first_date so there seems to be no reason to consider last_date.

edited Feb 25, 2021 at 14:44

answered Feb 25, 2021 at 14:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

astentx Over a year ago

Should be order by first_date, last_date: by the First and Last Date

Joe Taras · Accepted Answer · 2021-02-25 15:10:25Z

0

Try this:

select x.ID,  x.First_Date,  x.Last_Date,   x.Age_Days,
   row_number() over (partition by x.id order by first_date) as New_Column
from x

You may consider a rank type function if first_date duplicates

edited Feb 25, 2021 at 15:10

Joe Taras

15.4k7 gold badges45 silver badges60 bronze badges

answered Feb 25, 2021 at 15:09

Jatin Morar

1501 silver badge7 bronze badges

Collectives™ on Stack Overflow

Select Statement Create Sequence Variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related