Select unique records from a table

I have a table called 'Users' with the following structure and data

Code     Name      EFFECTIVE_DATE (dd/mm/yyyy)
=====================
h1        Tommy    1/5/2005
h2        Alex         1/6/2005
h2        Alex         15/6/2005

I need *a query statement* that produces the following output

Code     Name      EFFECTIVE_DATE (dd/mm/yyyy)
=====================
h1        Tommy    1/5/2005
h2        Alex         15/6/2005

* The key is "get a record that has max value of EFFECTIVE_DATE, if duplicate"

I've thought of MAX(EFFECTIVE_DATE) can be used, but I don't know how to use it.

Regards,
Noon
Start Free Trial