Home » SQL & PL/SQL » SQL & PL/SQL » Generating a Sequence
Generating a Sequence [message #332599] Wed, 09 July 2008 02:28 Go to next message
bholaimmu
Messages: 35
Registered: October 2007
Member
I have the following SQL statement:
SELECT Instance, Phase, Start_Date,
End_Date, Resource
FROM vw_Info
ORDER BY Instance

It produces results as follows:
Instance Phase Start_Date End_Date Resource
1001-1 Phase 1 1/1/2008 1/31/2008 Jones, Jason
1001-2 Phase 1 1/1/2008 1/31/2008 Jones, Jason
1001-3 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-3 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-4 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-5 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-5 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-5 Phase 1 1/1/2008 1/31/2008 Smith, Jane
1001-6 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-7 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-7 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-8 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-8 Phase 1 1/1/2008 1/31/2008 Martin, Ray

How do I modify my query so it generates a sequence number that starts over when the Instance changes? It needs to produce the following:
Instance Seq Phase Start_Date End_Date Resource
1001-1 1 Phase 1 1/1/2008 1/31/2008 Jones, Jason
1001-2 1 Phase 1 1/1/2008 1/31/2008 Jones, Jason
1001-3 1 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-3 2 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-4 1 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-5 1 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-5 2 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-5 3 Phase 1 1/1/2008 1/31/2008 Smith, Jane
1001-6 1 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-7 1 Phase 1 1/1/2008 1/31/2008 Martin, Ray
1001-7 2 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-8 1 Phase 1 1/1/2008 1/31/2008 Garcia, Juan
1001-8 2 Phase 1 1/1/2008 1/31/2008 Martin, Ray

Its very urgent…

Thx N Regs,
Re: Generating a Sequence [message #332601 is a reply to message #332599] Wed, 09 July 2008 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is urgent for you is to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You have to know that NOTHING is urgent in forum and this word should not be used.

Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.


Regards
Michel
Re: Generating a Sequence [message #332645 is a reply to message #332601] Wed, 09 July 2008 04:56 Go to previous message
bholaimmu
Messages: 35
Registered: October 2007
Member
Thx for the solution.


Thx N Regs,
Khan.
Previous Topic: Grouping with Union statement
Next Topic: Conditional statement within Where clause subquery
Goto Forum:
  


Current Time: Fri Dec 02 12:45:36 CST 2016

Total time taken to generate the page: 0.05852 seconds