Here’s some code which demonstrates the usefulness of the Rank function in PL/SQL (can also be used in T-SQL):

Sample Data:

SELECT user_id,
     email_address,
     email_type,
 RANK () OVER (PARTITION BY user_id ORDER BY email_type, activity_date DESC) rnk
FROM email_table

Output:

(Notice on the two records where user_id = 5271 and email_type = ‘A’ the rank of 1 is given to the record with the most current activity_date. This is because the ORDER BY portion of the RANK function sorts the records for each unique user_id first by email_type and second by activity_date. This makes it possible to select the most current record)

And with this code we can grab only the email addresses with a type of ‘A’ and the most current activity_date by using the WHERE rnk = 1 clause:

SELECT email_address FROM
 (SELECT user_id,
       email_address,
       email_type,
  RANK () OVER (PARTITION BY user_id ORDER BY email_type, activity_date DESC) rnk
 FROM email_table)
WHERE rnk = 1;

Output: