CODEWITHSUNDEEP
sql
TSES
TSES

Reputation: 3

How can I run a vlookup function in SQL within the same table?

I'm fairly new to SQL and struggling to find a good way to run the following query.

I have a table that looks something like this:

NAME       JOB GRADE     MANAGER NAME
X          7             O
Y          6             X
Z          5             X
A          4             Z
B          3             Z
C          2             Z

In this table, it shows that Y and Z report into X, and A, B and C report into Z.

I want to create a computed column showing the grade each person's most senior direct report or "n/a" if they don't manage anyone. So that would look something like this:

NAME       JOB GRADE     MANAGER NAME     GRADE OF MOST SENIOR REPORT
X          7             O                6
Y          6             X                N/A
Z          5             X                4
A          4             Z                N/A
B          3             Z                N/A
C          2             Z                N/A

How would I do this?

Upvotes: 0

Views: 6324

Answers (2)

user330315
user330315

Reputation:

Something like this:

select name, job_grade, manager_name, 
       (select max(job_grade) from grades g2 
        where g2.manager_name = g1.name) as grade_of_most_recent_senior
from grades g1
order by name;

The above is ANSI SQL and should work on any DBMS.

SQLFiddle example: http://sqlfiddle.com/#!15/e0806/1

Upvotes: 0

Just code
Just code

Reputation: 13801

SELECT g.*,isnull(convert(nvarchar, (SELECT max(g2.GRADE) 
FROM dbo.Grade g2 WHERE 
g2.manager  =g.NAME  AND g2.NAME!=g.NAME    )),'N/A') as most_graded
FROM dbo.Grade g

The max will find out the topmost graded

Input

X   7   O
y   6   X
Z   5   X
A   6   Z
C   2   Z

Output

X   7   O   6
y   6   X   N/A
Z   5   X   6
A   6   Z   N/A
C   2   Z   N/A

Upvotes: 1

Related Questions