MSSQL Get maximum value from several columns by identifier

Question

I came across many similar posts, but none of them found this specific one.

Here is my details:

ID      CID     NARID   NATID       NADate      EID     AEDate
1       1655    1       4           12/1/12     202     6/4/14 11:37:01
2       1655    1       7           12/1/12     202     6/4/14 11:37:12
5       1655    2       65          1/13/14     587     6/4/14 11:37:00
29      3165    1       6           4/15/14     7       6/4/14 11:37:00
300     3165    1       6           6/30/14     7       6/4/14 11:33:50
295     3165    2       64          6/11/14     7       6/4/14 11:37:00
302     3165    2       63          7/24/14     7       6/4/14 11:41:24
303     3165    2       67          7/24/14     7       6/4/14 15:59:06

First, I am looking to get the maximum NADate for each CID and NARID:

ID      CID     NARID   NATID       NADate      EID     AEDate
1       1655    1       4           12/1/12     202     6/4/14 11:37:01
2       1655    1       7           12/1/12     202     6/4/14 11:37:12
5       1655    2       65          1/13/14     587     6/4/14 11:37:00
300     3165    1       6           6/30/14     7       6/4/14 11:33:50
302     3165    2       63          7/24/14     7       6/4/14 11:41:24
303     3165    2       67          7/24/14     7       6/4/14 15:59:06

Then from these results, take the record with the maximum AEDate (along with all the other relevant fields):

ID      CID     NARID   NATID       NADate      EID     AEDate
2       1655    1       7           12/1/12     202     6/4/14 11:37:12
5       1655    2       65          1/13/14     587     6/4/14 11:37:00
300     3165    1       6           6/30/14     7       6/4/14 11:33:50
303     3165    2       67          7/24/14     7       6/4/14 15:59:06

The type of database is MSSQL 2005.

Answer 1

I think the easiest way is to use dense_rank():

select t.*
from (select t.*,
             dense_rank() over (partition by cid
                                order by nadate desc, cast(edate as date) desc
                               ) as seqnum
      from table t
     ) t
where seqnum = 1;

cast(edate to date), edate. dense_rank(), .

Answer 2

row_number() (cid, narid). , nadate desc, aedate desc, 1 , :

select  *
from    (
        select  row_number() over (
                    partiton by cid, narid
                    order by nadate desc, aedate desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn = 1

Answer 3

    WITH TEMP AS
        (    
        SELECT CID,NARID,MAX(NADATE)  AS TEMPDATE
        FROM TABLE
        GROUP BY CID,NARID
        )
    SELECT A.ID,A.CID,A.NARID,A.NATID,A.NADate,A.EID,MAX(A.AEDate)
      FROM TABLE A INNER JOIN TEMP 
    ON A.CID=TEMP.CID AND A.NARID=TEMP.NARID AND A.NADATE=TEMP.TEMPDATE
    GROUP BY A.ID,A.CID,A.NARID,A.NATID,A.NADate,A.EID;