Taking Last Element of Every User ID In SQL Table

Question

I am trying to figure out how to do the following in SQL (I'm specifically working in Teradata). Given the following table of user IDs, transaction date, and item bought, I'm trying to figure out how for each user ID to get the last item bought, for example:

User ID       Date            Product
 123         12/01/1996         A
 123         12/02/1996         B
 123         12/03/1996         C
 124         12/01/1996         B
 124         12/04/1996         A
 123         12/05/1996         D

So the query would return in this case:

User ID         Last Product Bought
  123                   D
  124                   A

And so forth. I tried using a Partition By or Window function in Teradata, but could not figure out how to implement it.

Thanks for your help.

Answer 1

Apply Teradata's proprietary syntax for filtering Windowed Aggregates:

select *
from tab
qualify
   row_number()
   over (partition by User_ID         -- each user
         order by Date_col desc) = 1  -- lastest row