Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    20

    Unanswered: Return Single Row for highest value

    DB2 9.5 LUW

    I have a table with multiple entries for a user - i want to return a row for each user with latest date.

    Example
    user - 1000
    Rows on table
    user - 1000
    start date - 2009-05-09
    grade - 5
    user - 1000
    start date - 2009-04-01
    grade - 4
    user - 1000
    start date - 2009-02-01
    grade -3

    I used max on the date but it did not work - nor did distinct

    Help would be appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Howardw View Post

    I used max on the date but it did not work - nor did distinct
    MAX() works for me just fine; if it did not for you - you must have used it incorrectly.

    I hope my answer is as specific as your question.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Howard,
    It is typically helpful to give what SQL you have tried, so we can show you what needs to be corrected to fit your needs. If it is a complex question then table layout and some sample data would, also, help.
    As Nick states the MAX() function works perfectly in all cases, so there is obviously something wrong with the SQL that you wrote or the data involved.
    Something like the following should do what you are after:
    select user, max(date)
    from table....
    where...
    group by user

    Dave

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb t1.start_date = max(t2.start_date)

    Quote Originally Posted by Howardw View Post
    DB2 9.5 LUW

    I have a table with multiple entries for a user - i want to return a row for each user with latest date.

    Example
    user - 1000
    Rows on table
    user - 1000
    start date - 2009-05-09
    grade - 5
    user - 1000
    start date - 2009-04-01
    grade - 4
    user - 1000
    start date - 2009-02-01
    grade -3

    I used max on the date but it did not work - nor did distinct

    Help would be appreciated.
    Code:
    select t1.* from table1 t1
    where start_date = (select max(t2.start_date) from table1 t2
                                   where t2.user = t1.user )
    What is easier then DB2 ?

    Lenny

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want a row for a specific user with latest date,
    you can use "ORDER BY start_date DESC" and "FETCH FIRST 1 ROW ONLY".

    Please see "Retrieving MAXimum row"
    in "SQL on Fire! Part 1":
    http://www.sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Nothing is new under Moon !

    Lenny

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Updated: Added AS q for DB2 9.1 for LUW or earlier.

    Sometimes, using OLAP specification is more efficient than using correlated scalar-fullselect(or self join) with MAX() function.
    Code:
    SELECT user , start_date , grade
      FROM (SELECT t.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY user
                                ORDER BY start_date DESC ) rn
              FROM table_sample t
           ) AS q
     WHERE rn = 1
    ;
    Last edited by tonkuma; 12-16-09 at 14:21.

Posting Permissions

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