Q

Join only latest row per user

I have three tables as defined below.

Table Users
UserID Name

fred   Fred Jones
joe    Joe Hill
mike   Mike green

Table Addresses UserID Type Address fred email fred@wherever.com fred web http://freds.website.com joe email joe@wherever.com mike email mike@wherever.com mike web http://mikes.website.com
Table Passwords UserID Password create_Date fred fredcurrent "latest timestamp" fred fredearlier "earlier timestamp" joe joecurrent "latest timestamp" mike mikecurrent "latest" mike mikeearlier "earlier"

Note that I didn't put actual dates in the create_Date column, please imagine that they exist. I would like a single select or view that gives me the following records where the current password is the one with the latest create_Date in the Passwords table. In other words, I want to see all addresses, but only the current password.


UserID   Name      Type       Address               Password

fred   Fred Jones  email  fred@wherever.com         fredcurrent
fred   Fred Jones  web    http://freds.website.com  fredcurrent
joe    Joe Hill    email  joe@wherever.com          joecurrent
mike   Mike green  email  mike@wherever.com         mikecurrent
mike   Mike green  web    http://mikes.website.com  mikecurrent

In the end I would like only one roundtrip request to Oracle/SQL to get this recordset. It doesn't seem all that unreasonable on the surface, but the answer isn't apparent to me.

Oracle/SQL? Are you writing an application that needs to be portable between Oracle and SQL Server? If so, you're in luck, the following solution will work in both databases.

First, let's write a simple join query:

select Users.UserID 
     , Users.Name
     , Addresses.Type   
     , Addresses.Address
     , Passwords.Password
  from Users
inner
  join Addresses
    on Users.UserID = Addresses.UserID
inner
  join Passwords 
    on Users.UserID = Passwords.UserID

As you no doubt discovered, this query exhibits cross join effects. Each UserID will have M*N rows, where M is the number of addresses and N is the number of passwords. All that's necessary here is simply to restrict the number N to be 1, i.e. so that each UserID to joins only one row from the Passwords table. This is accomplished by adding the following WHERE condition:

 where Passwords.create_Date  
     = ( select max(create_Date)
           from Passwords
          where UserID = Users.UserID )

One caveat here is that even though the subquery can only return one value, there might be two rows for the same UserID with the same create_Date. This might happen if the user changes her password twice on the same day. If this is a possibility, you'll want to use a DATETIME datatype for the create_Date column, and not a DATE datatype.

This was first published in May 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close