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: 65144
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: Logic help required
Next Topic: Controlling user's environment
Goto Forum:
  


Current Time: Sat Aug 19 11:55:48 CDT 2017

Total time taken to generate the page: 0.04317 seconds