Home » SQL & PL/SQL » SQL & PL/SQL » update flag with first occurence of a column
icon5.gif  update flag with first occurence of a column [message #206105] Tue, 28 November 2006 15:19 Go to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
Table T1 has following column
pk1 pk2 pk3 pk4 seq create_dt result FLAG

primary key (serial_pk1,pk2,pk3,pk4,seq)

pk1 pk2 pk3 pk4 sequence create_dt result FLAG
2309 N B 8837 1 08/04/2006 00:00:00 01 Y
2309 N B 8837 2 08/10/2006 00:00:00 01
2,309 N B 8837 3 08/10/2006 00:00:00 02 Y
2,309 N B 8837 4 08/10/2006 00:00:00 01
2,309 N B 8837 5 08/10/2006 00:00:00 02
2,309 N B 8837 6 08/11/2006 00:00:00 02




the flag should be updated to Y for the first occurence of result column for the value of pk1.
example : for pk1= 2309 , when value of result =01 , the flag is updated to Y , when again
the value of result becomes 01 for a latter sequence, no changes in flag.
So we need to sort by min (create_dt) .But if the create_dt is same (as in the case of 3rd and 5th record )
flag sud be made Y .. for the minimum sequence


Can anybody help on this query
Re: update flag with first occurence of a column [message #206107 is a reply to message #206105] Tue, 28 November 2006 15:33 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
the value of Flag is currently blank. The expected values are mentioned above.
Re: update flag with first occurence of a column [message #206145 is a reply to message #206105] Tue, 28 November 2006 23:30 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
analytics could help.
not tested, as i do not have no access to Oracle now.

SELECT a.pk1, a.pk2, a.pk3, a.pk4, a.sequence, a.create_dt, a.result, decode(a.rn, 1, 'Y') flag
  FROM (
    SELECT pk1, pk2, pk3, pk4, sequence, create_dt, result,
        row_number() over (partition by pk1, result order by create_dt, sequence) rn
      FROM t1
    ) a
  ORDER BY a.pk1, a.create_dt, a.sequence
Re: update flag with first occurence of a column [message #206246 is a reply to message #206145] Wed, 29 November 2006 05:15 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
How does it update the flag?
Thankx for the reply
Re: update flag with first occurence of a column [message #206250 is a reply to message #206105] Wed, 29 November 2006 05:45 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
update t1 a set flag = 'Y'
where create_date = 
(select min(b.create_date) from t1 b
where a.pk1 = b.pk1
and   a.flag = b.flag)
and seq =
(select min(b.seq) from t1 b
where a.pk1 = b.pk1
and   a.flag = b.flag
and   a.create_date = b.create_date)
Re: update flag with first occurence of a column [message #206254 is a reply to message #206250] Wed, 29 November 2006 05:50 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
the abov query dosnt mention anything about first occurence of result for each pk1.
I think that will not work..
Re: update flag with first occurence of a column [message #206257 is a reply to message #206105] Wed, 29 November 2006 05:53 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I got the wrong column. I meant:

update t1 a set flag = 'Y'
where create_date =
(select min(b.create_date) from t1 b
where a.pk1 = b.pk1
and a.result = b.result)
and seq =
(select min(b.seq) from t1 b
where a.pk1 = b.pk1
and a.result = b.result
and a.create_date = b.create_date)
Re: update flag with first occurence of a column [message #206260 is a reply to message #206254] Wed, 29 November 2006 05:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
I think that will not work

Try that out and see.
Quote:
the abov query dosnt mention anything about first occurence of result for each pk1
What is MIN for?

By
Vamsi
Re: update flag with first occurence of a column [message #206263 is a reply to message #206105] Wed, 29 November 2006 06:09 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I was basing it on your sample data and it does a perfect job on that:

SQL> select * from t1;

PK1   P PK3   PK4          SEQ CREATE_DT RE F
----- - ----- ----- ---------- --------- -- -
2309  N B     8837           1 08-APR-06 1
2309  N B     8837           2 08-OCT-06 1
2309  N B     8837           3 08-OCT-06 2
2309  N B     8837           4 08-OCT-06 1
2309  N B     8837           5 08-OCT-06 2
2309  N B     8837           6 08-NOV-06 2

6 rows selected.

SQL> 
SQL> update t1 a set flag = 'Y'
  2  where create_dt =
  3  (select min(b.create_dt) from t1 b
  4  where a.pk1 = b.pk1
  5  and a.result = b.result)
  6  and seq =
  7  (select min(b.seq) from t1 b
  8  where a.pk1 = b.pk1
  9  and a.result = b.result
 10  and a.create_dt = b.create_dt) ;

2 rows updated.

SQL> 
SQL> select * from t1;

PK1   P PK3   PK4          SEQ CREATE_DT RE F
----- - ----- ----- ---------- --------- -- -
2309  N B     8837           1 08-APR-06 1  Y
2309  N B     8837           2 08-OCT-06 1
2309  N B     8837           3 08-OCT-06 2  Y
2309  N B     8837           4 08-OCT-06 1
2309  N B     8837           5 08-OCT-06 2
2309  N B     8837           6 08-NOV-06 2


The MIN is there to get the earliest create_date in the grouping of (pk1, result) and earliest sequence within the grouping (pk1, result, create_date).
Previous Topic: Need Help for SQL Query
Next Topic: Solve this classic query
Goto Forum:
  


Current Time: Mon Dec 05 11:03:33 CST 2016

Total time taken to generate the page: 0.11436 seconds