SQL- Subquery with aggregate functions (SQL Server) -

my set thus: have 3 tables-

• students (studentid, firstname, lastname etc.),
• studentsemesters(studentid,semid etc.), ,
• semesters(semid, year)

my requirement to, details each student last semester. logically implies semester highest year number. cannot seem query right. simplicity 'year' integer (e.g. 2000, 1998). below current query have been stuck @ time:

select dbo.student.lastname + ' , ' + dbo.student.firstname student, dbo.student.defence1date, dbo.student.defence2date, count(studentsemesters_1.semid)             semestercount, dbo.student.entrysemester + ' - ' +                (select dbo.studentsemesters.semid                   dbo.studentsemesters inner join                                dbo.listsemesters on dbo.studentsemesters.semid = dbo.listsemesters.semid                 (dbo.student.studentcode = dbo.studentsemesters.studentcode)                 group dbo.studentsemesters.semid, dbo.listsemesters.year                 having (dbo.listsemesters.year = max(dbo.listsemesters.year))) expr1  dbo.student inner join            dbo.studentsemesters studentsemesters_1 on dbo.student.studentcode = studentsemesters_1.studentcode group dbo.student.lastname, dbo.student.firstname, dbo.student.defence1date, dbo.student.defence2date, dbo.student.entrysemester,             dbo.student.studentcode 

you can details last semester each student:

select    s.lastname + ' , ' + s.firstname   student,     s.defence1date,    s.entrysemester + ' - ' + s1.semid expr1  dbo.student               s inner join dbo.studentsemesters s1 on s.studentcode = s1.studentcode inner join dbo.listsemesters    lm on lm.semid      = s1.semid inner join  (     select semid, max(year) maxyear     dbo.studentsemesters     group semid ) s2 on s2.semid = lm.semid , ls.year = s2.maxyear; 

however, if you're using sql server 2005+, can use window function so:

with cte (     select        s.lastname + ' , ' + s.firstname   student,         s.defence1date,        s.entrysemester + ' - ' + s1.semid expr1,       row_number() over(partition s1.semid                         order year desc) rownumber      dbo.student               s     inner join dbo.studentsemesters s1 on s.studentcode = s1.studentcode     inner join dbo.listsemesters    lm on lm.semid      = s1.semid )  select    student,   defence1date,   expr1, cte rn = 1; 

but won't semestercount, might able make (this guess):

select    s.lastname + ' , ' + s.firstname   student,     s.defence1date,    s1.semcount,   s.entrysemester + ' - ' + s1.semid expr1  dbo.student               s inner join (    select studentcode, count(semid) semcount    dbo.studentsemesters     group studentcode ) s1 on s.studentcode = s1.studentcode inner join dbo.listsemesters    lm on lm.semid      = s1.semid inner join  (     select semid, max(year) maxyear     dbo.studentsemesters     group semid ) s2 on s2.semid = lm.semid , ls.year = s2.maxyear;