This is a typical situation for using a GROUP BY, since you want "summary information" from a complete table: the number of rows to show is less than the number of rows that contain the information.
Originally Posted by G_Andy
E.g., the row "Andy 1984" cannot be dropped by using a WHERE condition, since its presence or not in the result table depends on other rows, in this case the row "Andy 2005".
Since you want just one row per user, grouping by user is the way to go.
This will show you ( in the result table) a single row per group.
Final thing to decide: what do you want to see per group?
Well, clearly the "user" column, which is guaranteed to be constant in each group, and then, for the "year" column, the "summary information", which is MAX(year) in your case.
Last edited by Peter.Vanroose; 10-01-05 at 15:39.
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting