SQL - How to select data row with Maximum value wi

Question

In the process of testing a SQL query in preparation of creating a view, I 1st came up with this formula

select a.SchID as [SchID],  b.CourseID as [CourseID], a.EmpNo as [EmpNo], 
   b.ActStartDate as [ActStartDate], a.ValidTo as [ValidTo]
from [SQL3].[dbo].[Training_Record] a inner join 

(select z.schid, z.CourseID as [CourseID],max(z.ActStartDate) as [ActStartDate]
from [SQL3].[dbo].[Training_Schedule] z group by z.SchID, z.CourseID)as b

    on a.SchID = b.SchID 

-- to test data content
where EmpNo = '141281' and CourseID = '22'

The results gave me 2 rows:

| SchID | CourseID | EmpNo |       ActStartDate      |         ValidTo         |
--------------------------------------------------------------------------------
| 5000  |    22    | 14000 | 2018-06-11 00:00:00.000 | 2018-12-10 00:00:00.000 |
| 5022  |    22    | 14000 | 2018-08-08 00:00:00.000 | 2019-02-07 00:00:00.000 |

I wanted the 2nd row, whose ActStartDate is the largest to be the only one to appear.

| SchID | CourseID | EmpNo |       ActStartDate      |         ValidTo         |
--------------------------------------------------------------------------------
| 5022  |    22    | 14000 | 2018-08-08 00:00:00.000 | 2019-02-07 00:00:00.000 |

But due to the SchID having different numbers it ends up registering as separate entries. SchID is the only column that is shared among these 2 tables, so how do I tell SQL to ignore SchID reading and give me the display for just the 2nd row?

Answer 1

You can try below - using subquery

select a.SchID as [SchID],  b.CourseID as [CourseID], a.EmpNo as [EmpNo], 
   b.ActStartDate as [ActStartDate], a.ValidTo as [ValidTo]
from [SQL3].[dbo].[Training_Record] a 
inner join

(select z.schid, z.CourseID as [CourseID],max(z.ActStartDate) as [ActStartDate]
from [SQL3].[dbo].[Training_Schedule] z group by z.CourseID)as b
on a.SchID = b.SchID 

where EmpNo = '141281' and CourseID = '22' and 
b.ActStartDate in (select max([ActStartDate]) from [SQL3].[dbo].[Training_Schedule] z1 where b.courseid=z1.courseid group by z1.courseid)

OR You can try using row_number()

select * from 
(
select a.SchID as [SchID],  b.CourseID as [CourseID], a.EmpNo as [EmpNo], 
   b.ActStartDate as [ActStartDate], a.ValidTo as [ValidTo],row_number() over(partition by b.courseid order by b.ActStartDate desc) as rn
from [SQL3].[dbo].[Training_Record] a inner join 

(select z.schid, z.CourseID as [CourseID],max(z.ActStartDate) as [ActStartDate]
from [SQL3].[dbo].[Training_Schedule] z group by z.SchID, z.CourseID)as b

    on a.SchID = b.SchID 

-- to test data content
where EmpNo = '141281' and CourseID = '22'
)A where rn=1