In Teradata, with SQL, how do I compare multiple dates and select the latest date?

Question

Below is the table and the required output.

I want to select the ID,STATUS and DATE where the DATE is the latest date. If the DATE is the same, only select the records where STATUS = A.

I need to able to do this in Teradata with SQL.

Table:

ID        STATUS        DATE
5550500     A          11/02/2016
5550500     I          8/08/2008
5551688     A          23/08/2013
5551688     I          23/08/1997
5551688     I          23/08/2013
5551966     I          18/10/1999
5551966     I          24/10/2003
5551966     I          10/11/1997
5551966     I          2/09/2014

Required Output:

ID        STATUS        DATE
5550500    A           11/02/2016
5551688    A           23/08/2013
5551966    I           2/09/2014

Answer 1

Teradata supports a syntax extension to filter directly on the result of a Windowed Aggregate Function without need for a Derived Table:

select *
from tab
QUALIFY -- similar to HAVING after GROUP BY
   row_number() -- or RANK to return multiple rows with the same (max date & status)
   over (partition by id 
         order by date desc, status) = 1