How to get a Detail ID when doing a Group By on the Master ID (SQL Server 2005)

Is there an easy way to get the Detail ID when doing a Group By on the Master ID (SQL Server 2005)?

Here is what I am doing currently:

IF OBJECT_ID('tempdb..#ApplicantDetail') IS NOT NULL DROP TABLE #ApplicantDetail
CREATE TABLE #ApplicantDe开发者_StackOverflowtail (
    [ApplicantDetailID] [int] NOT NULL,
    [ApplicantMasterID] [int] NOT NULL,
    [DateEffective] [datetime] NOT NULL,
    [CorrespondenceTypeID] [int] NOT NULL,
    [Value] [varchar](20) NULL
    )
GO   
INSERT #ApplicantDetail VALUES (197542,37046,'2003-05-13 00:00:00.000',34,'8')
INSERT #ApplicantDetail VALUES (217963,41145,'1994-03-11 00:00:00.000',34,'')
INSERT #ApplicantDetail VALUES (217965,41145,'1994-03-21 00:00:00.000',34,'NS')
INSERT #ApplicantDetail VALUES (238961,45536,'2003-10-22 00:00:00.000',34,'')
INSERT #ApplicantDetail VALUES (238963,45536,'2003-12-03 00:00:00.000',34,'4')
INSERT #ApplicantDetail VALUES (244910,46764,'2003-12-03 00:00:00.000',34,'NS')

SELECT ad.ApplicantDetailID
FROM (
    SELECT ad.ApplicantMasterID, MAX(ad.DateEffective) AS MaxDateEffective
    FROM #ApplicantDetail AS ad
    GROUP BY ad.ApplicantMasterID) AS gbad
JOIN 
#ApplicantDetail AS ad
ON 
gbad.ApplicantMasterID = ad.ApplicantMasterID AND 
gbad.MaxDateEffective = ad.DateEffective
ORDER BY ad.ApplicantMasterID, ad.DateEffective

This works but it takes a lot of keystrokes. Is there some way to just return the ApplicantDetailID that goes with MAX(ad.DateEffective) in the GROUP BY SELECT statement?

If there is then it would elimante having to JOIN ON the key fields and go right to the ID.

Or is there an easier/simpler way to do the same pattern?

I do this pattern a lot and it would be nice to cut down on the keystrokes.

Thanks in advance.

---

Use:

WITH example AS (
  SELECT ad.applicantdetailid,
         ad.applicantmasterid, 
         ad.dateeffective,
         ROW_NUMBER() OVER(PARTITION BY ad.applicantmasterid
                               ORDER BY ad.dateeffective DESC) AS rank
    FROM #APPLICANTDETAIL ad)
  SELECT e.applicantdetailid
    FROM example e
   WHERE e.rank = 1
ORDER BY e.applicantmasterid, e.dateeffective

---

Well, if you are using SQL Server 2005, then you can do the following:

SELECT ApplicantDetailID 
FROM (
    SELECT *,  MAX(DateEffective) OVER(PARTITION BY ApplicantMasterID) AS MaxDateEffective
    FROM #ApplicantDetail ) AS ad
WHERE MaxDateEffective = DateEffective
ORDER BY ApplicantMasterID, DateEffective