Home » SQL & PL/SQL » SQL & PL/SQL » problem in assigning serial number to records fetched by a query
icon4.gif  problem in assigning serial number to records fetched by a query [message #236074] Wed, 09 May 2007 00:18 Go to next message
glakshkar
Messages: 7
Registered: September 2005
Location: PUNE
Junior Member
Hi All,

first of all i have attached the script file which will create required table & populate the data


I will first plot the scenario & then i will ask what exactly i want

I have a table with structure like this
Table Name : salary_history
Columns: EMP_NUMBER, EMP_SALARY, DATE_UPDATED

and Data in the table is
------------------------------------
|EMP_NUMBER|EMP_SALARY|DATE_UPDATED|
------------------------------------
|1001 |2000.00 |01/04/2005 |
|1003 |9000.00 |01/04/2005 |
|1002 |5000.00 |01/04/2005 |
|1001 |4000.00 |01/07/2005 |
|1003 |7000.00 |01/09/2005 |
|1001 |3000.00 |01/12/2005 |
|1002 |6000.00 |01/12/2005 |
|1001 |8000.00 |01/04/2006 |
|1002 |4000.00 |01/08/2005 |
|1004 |2000.00 |01/04/2005 |
|1005 |2000.00 |01/04/2005 |
------------------------------------

when i execute below qry
select * from salary_history order by emp_number,date_updated;
i get following output

---------------------------------------
|EMP_NUMBER |EMP_SALARY | DATE_UPDATED|
---------------------------------------
| 1001 | 2000.00 | 01/04/2005 |
| 1001 | 4000.00 | 01/07/2005 |
| 1001 | 3000.00 | 01/12/2005 |
| 1001 | 8000.00 | 01/04/2006 |
| 1002 | 5000.00 | 01/04/2005 |
| 1002 | 4000.00 | 01/08/2005 |
| 1002 | 6000.00 | 01/12/2005 |
| 1003 | 9000.00 | 01/04/2005 |
| 1003 | 7000.00 | 01/09/2005 |
| 1004 | 2000.00 | 01/04/2005 |
| 1005 | 2000.00 | 01/04/2005 |
---------------------------------------

till here everything is fine. Now i want a qry which will assign serial no/sequence to the the records returned by the query & this serial number should be reinitialized when emp_number changes. So that my result looks like this
---------------------------------------
n|EMP_NUMBER|EMP_SALARY |DATE_UPDATED |
---------------------------------------
1| 1001 | 2000.00 | 01/04/2005 |
2| 1001 | 4000.00 | 01/07/2005 |
3| 1001 | 3000.00 | 01/12/2005 |
4| 1001 | 8000.00 | 01/04/2006 |
1| 1002 | 5000.00 | 01/04/2005 |
2| 1002 | 4000.00 | 01/08/2005 |
3| 1002 | 6000.00 | 01/12/2005 |
1| 1003 | 9000.00 | 01/04/2005 |
2| 1003 | 7000.00 | 01/09/2005 |
1| 1004 | 2000.00 | 01/04/2005 |
1| 1005 | 2000.00 | 01/04/2005 |
---------------------------------------

It's very urgent.
I want the qry for this ASAP Sad Crying or Very Sad

reply me fast


Re: problem in assigning serial number to records fetched by a query [message #236103 is a reply to message #236074] Wed, 09 May 2007 01:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hi,

First a small warning:
Quote:
It's very urgent.
I want the qry for this ASAP Sad Crying or Very Sad

reply me fast

Don't do this. Nobody posts here to get an answer next year. We really do not care if this is urgent to you.

Now, to your question.
You can use row_number() over (partition by emp_number order by date_updated)
as the column you want.
Re: problem in assigning serial number to records fetched by a query [message #236119 is a reply to message #236103] Wed, 09 May 2007 01:34 Go to previous message
glakshkar
Messages: 7
Registered: September 2005
Location: PUNE
Junior Member
first of all i m really sorry for using "urgent" word. I know this place is to share knowledge

I would like to thank u for providing me the solution.
It's working.

I had read this artical in asktom section, but i was unable to recollect it

once again Thnx a ton
Previous Topic: List schema tables in dependency order
Next Topic: String problem
Goto Forum:
  


Current Time: Tue Dec 06 14:14:17 CST 2016

Total time taken to generate the page: 0.28184 seconds