Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

increament tab column in sql via grouping

given this table structure and these data;
what is the fastest way to populate the mail column with an ordered number starting from 1 and incremented by 1 starting from the oldest date to the most recent


CREATE TABLE SENTTEST2
(
  REVIEW_NO  VARCHAR2(12 BYTE),
  DATE_SENT  DATE,
  MAIL       VARCHAR2(5 BYTE)
)


SET DEFINE OFF;
Insert into SENTTEST2
   (REVIEW_NO, DATE_SENT, MAIL)
 Values
   ('a1', TO_DATE('01/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into SENTTEST2
   (REVIEW_NO, DATE_SENT, MAIL)
 Values
   ('a1', TO_DATE('01/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into SENTTEST2
   (REVIEW_NO, DATE_SENT, MAIL)
 Values
   ('a1', TO_DATE('01/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into SENTTEST2
   (REVIEW_NO, DATE_SENT, MAIL)
 Values
   ('b2', TO_DATE('01/01/2001 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into SENTTEST2
   (REVIEW_NO, DATE_SENT, MAIL)
 Values
   ('b2', TO_DATE('01/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
Insert into SENTTEST2
   (REVIEW_NO, DATE_SENT, MAIL)
 Values
   ('b2', TO_DATE('01/01/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL);
COMMIT;

now looks like that

REVIEW_NO,DATE_SENT,MAIL
a1,1/1/2011,
a1,1/1/2010,
a1,1/1/2008,
b2,1/1/2001,
b2,1/1/2005,
b2,1/1/2007,



want it to look like that
REVIEW_NO,DATE_SENT,MAIL
a1,1/1/2011,3
a1,1/1/2010,2
a1,1/1/2008,1
b2,1/1/2001,1
b2,1/1/2005,2
b2,1/1/2007,3
0
it-rex
Asked:
it-rex
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Select review_no, date_sent, row_number() over(partition by review_no order by review_by date_sent desc) mail
From senttest2;
0
 
it-rexAuthor Commented:
how about the update?
0
 
slightwv (䄆 Netminder) Commented:
Sorry for that was on mobile and had tiny keyboard.  Thought select not update.

Try this
MERGE INTO senttest2 b
USING (
	select review_no, date_sent, row_number() over(partition by review_no order by date_sent desc) myrownum
	from senttest2
) e
ON (b.review_no = e.review_no and b.date_sent=e.date_sent)
WHEN MATCHED THEN
  UPDATE SET b.mail = e.myrownum
;

select * from senttest2;

Open in new window

0
 
it-rexAuthor Commented:
Thanks
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now