Re: SQL question

I have no idea if this will work. But I took the challenge and this is what I came up with.

First a function that gets the third salary from a list ordered by salaray descending, and then a query checking if an employees salary is equal to aor greater than it.

[Quoted] CREATE FUNCTION Max_Salary_3 (The_Dept IN NUMBER) IS

        The_Salary NUMBER;
BEGIN

	SELECT
		Salary INTO The_Salary
	FROM
		(SELECT
			ROWNUM	R,
			Salary
		FROM
			Employees
		WHERE
			Dept = The_Dept
		      AND ROWNUM <4
		ORDER BY
			Salary DESC)
              WHERE R = 3;

SELECT

	Dept,
	Salary
FROM
	Employees
WHERE
	Salary >= Max_Salary_3 (Dept)
ORDER BY
	Dept;

Brian

On Thu, 02 Nov 2000 12:14:19 GMT, xdba_at_my-deja.com wrote:

>Given an employees table like this,
>how could I query the top three salaried people per department inSQL?
>
>Dept Name Salary
>1 Smith 1000
>1 Jones 2000
>1 White 1000
>1 Black 3000
>1 Red 1000
>1 Blue 1000
>1 Smith 5000
>2 Apple 1000
>2 Pear 3000
>2 Peach 1000
>2 Lemon 4000
>2 Melon 2000
>2 Peach 3000
>2 Prune 1000
>etc.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
  Received on Thu Nov 02 2000 - 14:44:38 CET