Optimized query to get min/max

Question

I have data as

Employee:

id  Name
--------
1   xyz
2   abc
3   qaz

Employee_A: (Eid - employee table, title - title table)

eid    active    type    title
------------------------------
1     1         1        1
1     1         2        2
1     1         4        3
2     0         3        4
2     1         2        2
2     0         6        5
3     0         1        1
3     0         6        5
3     0         4        3

Title:

id  rank title_name
--------------------
1   1     Manager
2   1     Asst Manager
3   2     Staff1
4   3     Staff2
5   4     Staff3

My query looks like

SELECT name, t.title_name
FROM Employee e
INNER JOIN Employee_A a ON e.id = a.eid
INNER JOIN title t on a.title = t.id
WHERE a.type in (1,2) and active = 1
and t.rank = /* step 1 */
(select min(rank) FROM Employee_A a1 INNER JOIN title t1 ON a1.title = t1.id 
 WHERE a1.type in (1,2) and active = 1 AND a1.eid = e.id)
AND a.type = /* step 2 */
    ( select min(type) FROM Employee_A a2 INNER JOIN title t2 on a2.title = t2.id
      WHERE a2.type in (1,2) and active = 1 and a2.eid = e.id
     and t2.rank = 
         (select min(rank) FROM Employee_A a3 INNER JOIN title t3 ON a3.title = t3.id 
          WHERE a3.type in (1,2) and active = 1 AND a3.eid = e.id)
    )

I have more than 400K records and I'm trying to query minimum type with minimum rank (2 titles have same rank so need to select minimum title between them). My query is taking more time and I want to optimize it, is there a way i can avoid step 1, 2 and do it more simpler and fast?

Output:

Name   title_name
-----------------
xyz    Manager
abc    Asst Manager

Answer 1

I may be oversimplifying, but give this a shot:

select emp.name, MIN(a.title), MIN(t.rank)
from Employee emp
join Employee_A a on emp.id = a.eid
                  AND a.type in(1,2)
                  and a.active = 1
join title t on a.title = t.id
where not exists (select 1 from Employee_A where eid = emp.id and title < a.title and active = 1)
group by emp.name

Answer 2

Does this do what you need?

;WITH cte AS
(
SELECT name, t.title_name, 
  row_number() over (partition by e.id order by rank, type) rn
FROM Employee e
INNER JOIN Employee_A a ON e.id = a.eid
INNER JOIN title t on a.title = t.id
WHERE a.type in (1,2) and active = 1
)

select name,  title_name from cte
where rn=1

Or another alternative

SELECT e.name, t.title_name
FROM Employee e 
CROSS APPLY 
(
SELECT TOP (1) title_name 
FROM Employee_A a 
INNER JOIN title t on a.title = t.id
WHERE a.type in (1,2) and active = 1 AND e.id = a.eid
order by rank, type
) t