The Ultimate Correlated SQL Query

Posted: August 23, 2013 by Transformer in MS Access, SQL
Tags: , ,

Problem Statement:  There is a table named SkillSet which has employees’ skill details. We need to find out employees (skill wise) who have the highest proficiency rating. If multiple employees have the highest proficiency rating in some skill then the employee having more experience should be selected.

Employee Skills Proficiency Experience
Thor SQL 9 1
Thor VBA 8 1
Mike VBA 9 1
Mike SQL 6 1
Robert VBA 9 2
Robert C# 6 2
Robert SQL 9 1
Logan C# 9 1
Logan SQL 9 2

Solution:Lets do it in steps. Try to find out skill wise employees having maximum proficiency.


SELECT Employee, Skills, Proficiency
   FROM
    Skillset  as T1
WHERE Proficiency = (SELECT MAX(Proficiency) as Prof
                                             FROM
                                                  [Skillset] as T2
                                               WHERE T1.Skills = T2.Skills
                                            Group By Skills)

Above written query will return the following output:

 
Employee Skills Proficiency
Thor SQL 9
Mike VBA 9
Robert VBA 9
Robert SQL 9
Logan C# 9
Logan SQL 9

Here, you can see that for VBA and SQl there are multiple employees with the same (highest) proficiency. Now, to identify who of the highest proficiency employees has higher years of experience,
we need to add another level to the query like below:

SELECT Employee, Skills, Proficiency
   FROM
    Skillset  as T1 
WHERE  Experience = (
                      SELECT MAX(Experience) 
                          FROM SkillSet as T3 
                      Where Proficiency = (SELECT MAX(Proficiency) as Prof 
                                           FROM 
                                            [Skillset] as T2  
                                            WHERE T3.Skills = T2.Skills
                                            Group By Skills) AND T1.Skills = T3.Skills
                                     )

This query will return the output as below:

 
Employee Skills Proficiency
Robert VBA 9
Logan C# 9
Logan SQL 9

For another example of Correlated query, check this out:

Advertisements
Comments

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s