Use GROUP BY and return more than 2 columns

I am trying to find the latest unit_status, and the timestamp of that status (cdts), for every unid I have in a table.

This is what I have come up with so far:

with cte as 
(
    select * 
    from un_hi 
    where ag_id = 'bfd' and cdts > '201708' and
    unit_status != 'uc'
)
select distinct(unid), max(cdts2) Last_GPS_Report_Date
from cte
where unid in ('C06','C07','D01','D03','D04','D06')
group by unid
order by unid

It correctly returns every unid and the time of their latest status (max(cdts)), but I want to see what the unit_status was.

I have tried taking this query and joining it to the UN_HI table (which is where this data is coming from), but I couldn't get that to work. Any other suggestions?

You can use ROW_NUMBER:

with cte as 
(
    select  *,
            RN = ROW_NUMBER() OVER(PARTITION BY unid ORDER BY cdts2 DESC)
    from un_hi 
    where ag_id = 'bfd' 
    and cdts > '201708' 
    and unit_status != 'uc'
)
select *
from cte
where RN = 1;

StackExchange Database Administrators Q#183468, answer score: 10