-
stored proc help
I would like to create a package that contains to PL/SQL blocks:
department
dept_id location
1 London
2 New York
3 Singapore
4 Tokyo
employee
emp_id dept_id salary tier_id
1 2 20000 4
2 4 200000 1
3 3 25000 4
4 1 60000 2
5 1 30000 3
tier
tier_id tier_name
1 Director
2 Manager
3 Analyst
4 Intern
The first procedure will take in an department ID and will return both the current timestamp and a list of the different distinct tiers belonging to that department ID. These will be returned as two separate parameters.
The second stored procedure will take in a tier ID and provide back the second highest salary for that tier.
Any help would be appreciated
-
I forgot to mention each table has a timestamp column ts
Originally Posted by PaulH
I would like to create a package that contains to PL/SQL blocks:
department
dept_id location
1 London
2 New York
3 Singapore
4 Tokyo
employee
emp_id dept_id salary tier_id
1 2 20000 4
2 4 200000 1
3 3 25000 4
4 1 60000 2
5 1 30000 3
tier
tier_id tier_name
1 Director
2 Manager
3 Analyst
4 Intern
The first procedure will take in an department ID and will return both the current timestamp and a list of the different distinct tiers belonging to that department ID. These will be returned as two separate parameters.
The second stored procedure will take in a tier ID and provide back the second highest salary for that tier.
Any help would be appreciated
-
Hi
Create table statements and insert statements will help immensely.
check if thid sql does what you want play with it then you can wrap it in a stored proc
Code:
select
e.tier_id,
e.salary,
e.dept_id
from(
select
e.dept_id,
e,tier_id,
e.salary,
rank() over(partition by e.dept_id,e.tier_id) rank
from employee e
where dept_id=1) t
where rank=2
from t
i have a hunch your questions is ambiguous.
even with the formatting your question is not legible i am afraid
Last edited by hrishy; 10-01-2009 at 03:40 AM.
-
is that home work, isn't it?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
No its me trying to figure out to return back the second highest value in a list
since I want to throw away the lowest and highest values
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|