×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

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

(OP)
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!

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

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."

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

(OP)
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

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

Much clearer. Thanks.

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

#### CODE

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.

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."

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

(OP)
Worked BEAUTIFULLY!!!

Thanks

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!