SQL Query

[Kalyan Anand]

I have to write a SQL query

Assume, I have to choose 1 employee from each department for Sun Tech Days

Here are the conditions

Condition 1. First preference will be given to their Grade. 1 - Architect (most preferred) & 10 - Trainee (least preferred)

Condition 2. If there is tie between employees based on 1st condition, then it will be resolved by using the Registration.
Employees registered with Java Ranch get more priority (Y - Most preferred , N - Least preferred)

Condition 3. If there is tie based on conditions 1 and 2, then the employee who joined the company earlier would be chosen
i.e. Employee with Minimum Employee Id

-----------------------------------------------
Department | Empid | Registered | Grade
-----------------------------------------------
100 | 1001 | Y | 1
100 | 1002 | Y | 1
100 | 1003 | Y | 2
-----------------------------------------------
101 | 2001 | Y | 1
101 | 2002 | Y | 1
101 | 2003 | N | 1
-----------------------------------------------
102 | 3001 | N | 10
102 | 3002 | N | 10
102 | 3003 | N | 10
-----------------------------------------------
How do I write a single select query for this ?
[ March 22, 2007: Message edited by: Santhosh Jali ]

[Mark Spritzler]

Is this a test or a game that you are playing at the conference, that if we gave you the answer would be cheating?

Mark

[Srikanth Basa]

Originally posted by Santhosh Jali:
I have to write a SQL query

Assume, I have to choose 1 employee from each department for Sun Tech Days

Here are the conditions

Condition 1. First preference will be given to their Grade. 1 - Architect (most preferred) & 10 - Trainee (least preferred)

Condition 2. If there is tie between employees based on 1st condition, then it will be resolved by using the Registration.
Employees registered with Java Ranch get more priority (Y - Most preferred , N - Least preferred)

Condition 3. If there is tie based on conditions 1 and 2, then the employee who joined the company earlier would be chosen
i.e. Employee with Minimum Employee Id

-----------------------------------------------
Department | Empid | Registered | Grade
-----------------------------------------------
100 | 1001 | Y | 1
100 | 1002 | Y | 1
100 | 1003 | Y | 2
-----------------------------------------------
101 | 2001 | Y | 1
101 | 2002 | Y | 1
101 | 2003 | N | 1
-----------------------------------------------
102 | 3001 | N | 10
102 | 3002 | N | 10
102 | 3003 | N | 10
-----------------------------------------------
How do I write a single select query for this ?

[ March 22, 2007: Message edited by: Santhosh Jali ]

Check if this helps

SELECT empid FROM
(
SELECT
(RANK() OVER
(PARTITION BY department
ORDER BY
grade asc ,
registered desc ,
empid asc )
) as ranking ,
empid
FROM
employees_table
)
WHERE ranking = 1