Find the minimum number for a field then change all of the following numbers, if necessary 1

[borisbe]

Hey

I have data where there could be 1 pattern or multiple patterns.

set pattern
1 1
1 2
1 3
2 2
2 4
3 3

Is there a way in a case when, if or some other expression that I can write that would find the minimum pattern for a set and always make it number 1 then change the sequence if necessary so I would get:

set pattern
1 1
1 2
1 3
2 1
2 2
3 1

Thank you!

 

[SantaMufasa]

Sorry, Borisbe...I don't follow your specifications as they correspond to the resulting data set. Could you please clarify for us?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[borisbe]

Sorry, I probably just didn't give enough information or didn't explain it very well. Our database has the field called set but I would say it is more of a unique identifier for a person and the pattern# corresponds to a meeting date. A person has a meeting date planned for certain date(s) but if the date(s) need to be rescheduled then the data entry person removes the pattern# row and inserts a new pattern# so that is why person #2 (set #2) has their very first pattern# as 2 instead of 1 and they must have had to change another date which had pattern#3 so that is why their next one is pattern# 4. Person #3 had to reschedule twice so that is why their initial date has pattern#3.

set# pattern# dates
1 1 1/12
1 2 2/13
1 3 2/20

2 2 2/28
2 4 3/12

3 3 2/27

Is there a way in a case when, if or some other expression in a select statement that I can write that would find the minimum pattern for a set and always make it number 1 then change the sequence if necessary so I would get:

set pattern# dates
1 1 1/12
1 2 2/13
1 3 2/20

2 1 2/28
2 2 3/12

3 1 2/27

Thanks for your help!!!

 

[SantaMufasa]

Much clearer. Thanks.

There are many ways to achieve your goal. Here is one of the syntactically simpler ways that I can think of:

First, to confirm the data set:

select * from sets;

      SET#   PATTERN# DATE
---------- ---------- ----
         1          1 1/12
         1          2 2/13
         1          3 2/20
         2          2 2/28
         2          4 3/12
         3          3 2/27

6 rows selected.

Second, to meet your need:

select set#
    ,row_number() over (partition by set# order by pattern#) pattern#
    ,dates
from sets;

      SET#   PATTERN# DATE
---------- ---------- ----
         1          1 1/12
         1          2 2/13
         1          3 2/20
         2          1 2/28
         2          2 3/12
         3          1 2/27

6 rows selected.

In the above solution, "row_number() over (partiion..." is one of the very handy Oracle Analytic functions.

Let us know if this resolves your need, or if you have follow-up questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[borisbe]

Worked BEAUTIFULLY!!!

Thanks