OraFAQ Forum: SQL & PL/SQL » Increment sequnce no based on calender date.

Home » SQL & PL/SQL » SQL & PL/SQL » Increment sequnce no based on calender date.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Increment sequnce no based on calender date. [message #596552]

Tue, 24 September 2013 13:07

nisaros
Messages: 1
Registered: September 2013
Location: Chennai

Junior Member

Hi There,
I have a scenario wherin i need to be increasing the sequence no by 1 for every calender date.
For example lets say if i receive 5 dumps of data for 24/09/2013 it should be as below. For next day the 25th the sequence no should again begin with 1.

24/09/2013 1
24/09/2013 2
24/09/2013 3
24/09/2013 4
24/09/2013 5

25/09/2013 1

Please help

[Updated on: Tue, 24 September 2013 13:07]

Report message to a moderator

Re: Increment sequnce no based on calender date. [message #596553 is a reply to message #596552]

Tue, 24 September 2013 13:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Increment sequnce no based on calender date. [message #596557 is a reply to message #596552]

Tue, 24 September 2013 13:13

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How is this a SQL question?

Report message to a moderator

Re: Increment sequnce no based on calender date. [message #596566 is a reply to message #596552]

Tue, 24 September 2013 14:39

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

nisaros wrote on Tue, 24 September 2013 23:37

I have a scenario wherin i need to be increasing the sequence no by 1 for every calender date.
For example lets say if i receive 5 dumps of data for 24/09/2013 it should be as below. For next day the 25th the sequence no should again begin with 1.

24/09/2013 1
24/09/2013 2
24/09/2013 3
24/09/2013 4
24/09/2013 5

25/09/2013 1

You can do this using Analytic function. Use DENSE RANK, then PARTITION BY the date column and ORDER BY a proper column. For each date group you will have a rank as a pseudo column. Use the pseudo column as sequence.

Dense Rank

Regards,
Lalit

Report message to a moderator

Re: Increment sequnce no based on calender date. [message #596567 is a reply to message #596566]

Tue, 24 September 2013 14:52

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How do you use dense_rank from date of dumps????
Who say these dates are in a table?
Do you know anything about the environment?
One more answer you could omit until you know something about what there is.

Report message to a moderator

Re: Increment sequnce no based on calender date. [message #596569 is a reply to message #596567]

Tue, 24 September 2013 15:02

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In addition, dense_rank could NOT give the wanted result as all rows with same date will have the same number which is the opposite of what OP wants.

Report message to a moderator

Re: Increment sequnce no based on calender date. [message #596575 is a reply to message #596569]

Tue, 24 September 2013 17:05

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Hi nisaros, welcome to OraFAQ.

I think Lalit was right to direct you to Analytic Functions. He was probably thinking of the RANK() function rather than DENSE_RANK(), although I would have suggested ROW_NUMBER() as it is more descriptive of what you are trying to achieve.

On the odd chance that you came to an Oracle forum to ask a non-Oracle related question, I apologise for cluttering the forum with yet another response that doesn't answer your question.

Ross Leishman

Report message to a moderator

Previous Topic:	how to get Max(value) with its corresponding datetime ...
Next Topic:	pl/sql date format

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:51:51 CDT 2024