Posts Tagged ‘Correlated Query’

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. (more…)

Problem Statement: There are two tables and we need to get all the records from Table1 that are not in Table2.

   Table1                           Table2
Emp_id	Salary			Emp_id	Name
1	10000			1	Tom
2	23000			2	Rock
4	40000			3	Thor
9	34000				

Excluding Records from Table1 that Exist in Table2

Solution: It can be done in two ways, using Left Join OR using Exists condition. (more…)

RANK in MS Access

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

Sometimes one needs to rank data based on some field e.g. Students’ Marks, Product Sales etc. In different platforms there are different ways to do it.
In SQL Server and Excel there is RANK function that does the job but in MS Access there is no such built-in function.

However same result can be achieved using a correlated query.

e.g. Consider a dataset of student marks below.

Students Marks
Tony 34
Bob 32
Thor 48
Jack 42
Tom 41
Kate 45
Sid 26

Suppose one wants to rank students based on their marks (rank = 1 for highest marks). Following query can be used to rank them.

SELECT Students,
       (SELECT COUNT(T1.Marks)
                 [Table] AS T1
         WHERE T1.Marks >= T2.Marks) AS Rank
      [Table] AS T2

Following is the result of the query.

Students Rank
Thor 1
Kate 2
Jack 3
Tom 4
Tony 5
Bob 6
Sid 7

If,  rank 1 is to be assigned to the lowest value, then in that case the query above can be slightly modified to:

SELECT Students,
       (SELECT COUNT(T1.Marks) 
                 [Table] AS T1 
         WHERE T1.Marks <= T2.Marks) AS Rank 
      [Table] AS T2