Number generation based on 3 columns

Question

Number generation based on 3 columns

ravisankar

Old Hand

Points: 339
More actions
April 29, 2020 at 5:49 pm

#3747410
Sorry for the inconvenience
I have table1
Col1 Col2 update_date
Zzz ab 04/04/2020
Zzz ab 05/04/2020
Zzz dd 08/04/2020
BCD bc 09/04/2020
CDE cd 11/04/2020
CDE cd 12/04/2020
Also have extra 4 columns
I need result
Res Col1 Col2 update_date
1 Zzz ab 04/04/2020
1 Zzz ab 05/04/2020
2 Zzz dd 08/04/2020
3 BCD bc 09/04/2020
4 CDE cd 11/04/2020
4 CDE cd 12/04/2020
I need to generate Res column
Based on date, col1 and col2

Thanks in Advance
- This topic was modified 4 years, 8 months ago by ravisankar.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246990 More actions · Answer 1

ravisankar wrote:

I have table1
Col1 Col2 update_date
ABC ab 04/04/2020
ABC ab 05/04/2020
ABC ab 08/04/2020
BCD bc 09/04/2020
CDE cd 11/04/2020
CDE cd 12/04/2020
Also have extra 4 columns
I need result
Res Col1 Col2 update_date
1 Zzz ab 04/04/2020
1 Zzz ab 05/04/2020
2 Zzz dd 08/04/2020
3 BCD bc 09/04/2020
4 CDE cd 11/04/2020
4 CDE cd 12/04/2020
I need to generate Res column
Based on date, col1 and col2
Thanks in Advance

What is the logic for converting ABC to Zzz?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

drew.allen SSC Guru Points: 76997 More actions · Answer 2

And why does the col2 value switch from ab to dd on 08/04/2020?

It looks like you want a DENSE_RANK(), but the exact details depend on your answer to Phil`s question.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ravisankar Old Hand Points: 339 More actions · Answer 3

Sorry for the inconvenience

I have updated

This reply was modified 4 years, 8 months ago by ravisankar.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I'm guessing that you want all rows that share the same values of Col1 and Col2 to have the same number, and you want those numbers to be in order of the first update_date within each such partition. Is that right? What will happen if two partitions have the same first update_date? Since you haven't provided any table DDL or consumable sample data, I can't write the query for you. But I'll talk you through it:

(1) Use a CTE with all three columns of the table, plus a fourth, min_date, which will be the minimum date when partitioned by Col1 and Col2

(2) Use DENSE RANK, partitioned by Col1 and Col2 and ordered by min_date to generate the numbers

John