View Single Post
  #3 (permalink)  
Old September 20th, 2003, 01:52 PM
Jeff Mason Jeff Mason is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Your specification doesn't make it clear that there is more data in each row besides the 3 key columns. The second paragraph seems to indicate that the 3 columns are all that there is, and if that is so, then Beth's solution above is correct.

But, your third paragraph asks to retrieve the "...records for each UserId based on the latest UserDate for each record...", and I read that to mean there is more data in the row besides those 3 columns, and you want to retrieve the whole row. To identify the row that contains the maximum UserDate for each user requires what's called a 'correlated subquery' - this is a query which gets executed for each row in the outer, containing query:
Code:
SELECT UserID, UserName, UserDate, ... <theothercolumns> ... FROM yourtable AS T1
    WHERE T1.UserDate=
        (SELECT MAX(UserDate) FROM yourtable
            WHERE UserID=T1.UserID AND UserName=T1.UserName);
BTW, I would guess that your UserID and UserName columns refer to the same User; if this is so, then you are unnecessarily duplicating information, and your table design violates so-called 2nd normal form. This is a principle of database design which states that redundant information should be extracted into it's own table, and a relationship between the two tables defined. Thus, you should perhaps have one table defining UserID and the associated UserName, and another which lists the UserDate data for each UserID along with any other information pertaining to the UserID and Date combination.

Database normalization is not a set of hard and fast rules, but rather guidelines which experience has shown reduces all sorts of problems when data has to retrieved or updated.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote