|
Subject:
|
SQL syntax for my query - guru needed
|
|
Posted By:
|
badgolfer
|
Post Date:
|
9/20/2003 5:02:53 AM
|
I am a beginner and need help with an Access SQL statment.
I have a simple Access table with 3 columns in it. They
are UserId, UserName, UserDate. All 3 fields form a combination
key - so we can have records with the same UserId and
UserName but unique UserDate values.
The question: what is the SQL syntax to retrieve the
records for each UserId based on the latest UserDate
for each record? (ie one record per user that has the
latest date). Any help appreciated.
|
|
Reply By:
|
BethMoffitt
|
Reply Date:
|
9/20/2003 12:10:06 PM
|
Replace tblUsers with the proper name of your table in all instances and this would provide unique values with the max date:
SELECT tblUsers.UserID, tblUsers.UserName, Max(tblUsers.UserDate) AS MaxDate
FROM tblUsers
GROUP BY tblUsers.UserID, tblUsers.UserName;
Regards,
Beth M
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
9/20/2003 1:52:16 PM
|
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:
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
|
