×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

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

Students Click Here

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

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?

santaMufasa
(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

Thanks for your help!!!

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.

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.

santaMufasa
(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!!!

Thanks2thumbsup

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close