How to select the latest rows where each contains one value of all possible values found in a domain table?

ID Username City        RegisterYear
1  User1    New York    1990
2  User2    San Diego   2008
3  User3    Chicago     2009
4  User4    Los Angeles 1994
5  User5    San Diego   2004 

Domain Table

ID  City
1   New York
2   San Diego
3   Los Angeles
4   Chicago

In this example 开发者_Python百科the query would return:

User1
User2
User3
user4

Well, first you need to replace "City" in the first table with the ID column of the domain table. The "City" is not the primary key.

After that:

SELECT u.username
FROM   users u,
       domain d
WHERE  d.id = u.cityid
       AND u.registeryear = (SELECT MAX(u2.registeryear)
                             FROM   users u2
                             WHERE  u2.cityid = u.cityid);

Since the question doesn't provide an awful lot of detail, I have used some of the assumption that syrion made plus also decided that the city name will be unique to make things easier.

select max(u.username) keep (dense_rank last order by registeryear) as username
from 
  domain d
  left outer join users u on (d.city = u.city)
group by d.city