Fetching busy students and their latest product I use SQL Server 2014 and I have three tables:


Student (ID, Name, Age)
Product  (ID, Name)
StudentProduct (StudentID, ProductID, Date)  


TABLE Student(ID, Name, Age) &gt;&gt;
ID  Name    Age
1   Adam    20
2   Bob     17
3   Ben     18
4   Joseph  18 
5   Jon     18

TABLE Product (ID, Name) &gt;&gt;
ID  Name
1   SQLServer
2   Visual Studio
3   Idea
4   Eclipse

TABLE StudentProduct (StudentID, ProductID, Date) &gt;&gt;
StudentID   ProductID     Date
1           1             25.05.2016 0:00:00
1           4             26.06.2016 0:00:00
1           3             27.06.2016 0:00:00
1           2             28.06.2016 0:00:00
2           1             29.07.2016 0:00:00    


I've written a query to get all students who meet these criteria


Are older than 19
Participate in developing more than 3 products


Both criteria should be checked in a single query.

The desired result should then contain the 


Age of the student
Name of student
Name of the last developed product (using StudentProduct.Date)


Result should be:

Age   StudentName      ProductName
20    Adam             Visual Studio


My query is:

select e.age, e.name ,
(select top 1 p.Name from Product p
inner join StudentProduct peOut on p.ID = peOut.ProductID
where peOut.StudentID = e.ID
order by peOut.Date desc)
from Student e where e.age &gt; 19
and exists (select pe.StudentID, count(1) from StudentProduct pe
where pe.StudentID = e.ID group by pe.StudentID
having count(1) &gt; 3)


How can I improve the efficiency of my query?
Without additional info about indexes and row counts it's hard to tell, but the most elegant solution is probably using Analytical Functions (using employees instead of students):

with cte as
 (
    select s.age, s.name as EmployeeName, p.Name as ProductName,
       -- row with the newest date
       row_number() over (partition by s.ID order by sp.Date desc) as rn,
       -- number of Products per employee
       count(*) over (partition by s.ID) as cnt
    from Student as s 
    join StudentProduct as sp
      on sp.EmployeeID = e.ID
    join Product as p
      on p.ID = sp.ProductID
    where s.age &gt; 19
 )
select age, EmployeeName, ProductName
from cte
where cnt &gt; 3  -- more than three products
  and rn = 1   -- the latest product