MSSQL Get max value from multiple columns by an ID

greatest-n-per-group, sql, sql-server, top-n

Solution

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;

You need the `cast(edate to date)` so the query will be considering only the date portion of `edate`. You need the `dense_rank()` so the will return all rows on the most recent date.

Problem

I've come across many similar posts on this but none I've found got this specific. Here's my sample data: ``` 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 ``` I first am looking to get the max NADate for each CID & 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, get the record with the max AEDate (along with all other corresponding 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 database type is MSSQL 2005.

Original source