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

Quote:

---

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

is that home work, isn't it?

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