We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

increament tab column in sql via grouping

it-rex
it-rex asked
on
Medium Priority
273 Views
Last Modified: 2012-08-14
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Select review_no, date_sent, row_number() over(partition by review_no order by review_by date_sent desc) mail
From senttest2;

Author

Commented:
how about the update?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.