Results 1 to 3 of 3

Thread: Simple query?!

  1. #1
    Join Date
    Apr 2004
    Location
    Toronto
    Posts
    3

    Question Unanswered: Simple query?!

    Hello,

    My gues is there should be a simple query to do this, but...
    How would you select the name of EACH user and the MOST RECENT year only for each and every one?
    The table would look like this:

    user year
    ---- ----
    Andy 1984
    John 1982
    Lisa 2000
    John 2003
    Andy 2005
    John 2005

    And the query should yield something like:

    user year
    ---- ----
    Lisa 2000
    Andy 2005
    John 2005

    Thank you,
    Andy G.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    It is a simple query, and I'm sure you can figure it out.
    What have you got coded?

    HINT: Use the MAX() function.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by G_Andy
    How would you select the name of EACH user and the MOST RECENT year only for each and every one?
    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.
    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 16:39.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •