tsql - AdventureWorks2014 - Create an extract of latest hires based upon Job title -

i wondering if help.

i trying write code returns list of latest hires based upon jobtitle using adventureworks2012 databse.

so far, have following:

    select distinct hredh.businessentityid,     hre.jobtitle,     hre.hiredate [humanresources].[employeedepartmenthistory] hredh inner join humanresources.employee hre on hredh.businessentityid = hre.businessentityid     , hre.businessentityid = (         select top 1 businessentityid         humanresources.employee hre2         hre2.jobtitle = hre.jobtitle         order hiredate desc         ) order hre.jobtitle 

this appears work fine, sure there better way (without use of select distinct @ beginning of statement)

i trying best learn sql myself, vast pool of knowledge on here appreciated!

thanks,

this return rows join of 2 tables hire date job title equal highest date job title.

select     hredh.businessentityid     ,hre.jobtitle     ,hre.hiredate [humanresources].[employeedepartmenthistory]  hredh     join humanresources.employee hre         on hredh.businessentityid = hre.businessentityid hre.hiredate = (select max(hiredate) humanresources.employee _hre hre.jobtitle = _hre.jobtitile) order hre.jobtitle