Results 1 to 10 of 10

Thread: SQL Question

  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: SQL Question

    DB2 9.1/AIX 5.3
    Assuming a table has 3 columns and sample data is as below

    Time Name Location
    ---------- ------ --------
    02:03:02 Alan AFRICA
    04:03:02 Alan ASIA
    05:03:02 Alan NA
    07:27:48 Alan ASIA
    01:09:10 Tom EUROPE
    02:09:10 Tom ASIA
    05:09:10 Tom AUS
    12:09:10 Tom AFRICA
    15:09:10 Tom AUS
    05:06:58 Nancy AFRICA
    08:06:58 Nancy ASIA
    12:06:58 Nancy NA
    15:06:58 Nancy AFRICA
    17:33:06 Mike EUROPE

    I want the following as output from a select statement
    Time Name Location
    ---------- ------ --------
    07:27:48 Alan ASIA
    15:09:10 Tom AUS
    15:06:58 Nancy AFRICA
    17:33:06 Mike EUROPE

    If I need just Time & Name I can do select max(time), name from xxx group by name. How to code this? Pls advice

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use a correlated subquery:
    Code:
    select
    ...
    from table t1
    where t1.time = (select max(time) from table1 where user=t1.user)
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try something like this:

    Code:
    with t1 (time,name) as 
    (select max(time),name from mytable group by name)
    select t.* from mytable as t 
    inner join t1 on (t.time = t1.time and t.name = t1.name)
    Andy

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    With OLAP function, it will be not neccesary to scan table(or index) twice.
    Code:
    SELECT Time, Name, Location
      FROM (SELECT t.*
                 , ROW_NUMBER()
                     OVER(PARTITION BY Name
                          ORDER BY Time DESC) rn
              FROM sample_data t
           ) t
     WHERE rn = 1
    ;

  5. #5
    Join Date
    Jul 2009
    Posts
    150
    What will happen if we have two or more enties with the same max time for same location:
    15:09:10 Tom AUS
    15:09:10 Jerry AUS ?

    I suppose to get the max name for remove the duplicates:

    select distinct mn.*
    from
    tbl_users nm
    join table
    (select max(n2.time) as mtime, max(n2.name) mname
    from tbl_users n2
    where n2.location = nm.location) tt
    On
    tt.mtime = nm.time
    and
    tt.mname = nm.name;

    Kara Sw.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Kara, you are wrong.

    where n2.location = nm.location
    should be
    where n2.name = nm.name

    and distinct is not neccesary.

    using OP's data:
    Code:
    VALUES
     ('02:03:02', 'Alan',  'AFRICA')
    ,('04:03:02', 'Alan',  'ASIA'  )
    ,('05:03:02', 'Alan',  'NA'    )
    ,('07:27:48', 'Alan',  'ASIA'  )
    ,('01:09:10', 'Tom',   'EUROPE')
    ,('02:09:10', 'Tom',   'ASIA'  )
    ,('05:09:10', 'Tom',   'AUS'   )
    ,('12:09:10', 'Tom',   'AFRICA')
    ,('15:09:10', 'Tom',   'AUS'   )
    ,('05:06:58', 'Nancy', 'AFRICA')
    ,('08:06:58', 'Nancy', 'ASIA'  )
    ,('12:06:58', 'Nancy', 'NA'    )
    ,('15:06:58', 'Nancy', 'AFRICA')
    ,('17:33:06', 'Mike',  'EUROPE')
    )
    select nm.*
      from tbl_users nm
      join table
           (select max(n2.time) mtime
                 , max(n2.name) mname
              from tbl_users n2
             where n2.location = nm.location) tt
       On  tt.mtime = nm.time
       and tt.mname = nm.name
    ;
    ------------------------------------------------------------------------------
    
    TIME     NAME  LOCATION
    -------- ----- --------
    15:09:10 Tom   AUS     
    12:06:58 Nancy NA      
    
      2 record(s) selected.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH 
     tbl_users(Time, Name, Location) AS (
    VALUES
     ('02:03:02', 'Alan',  'AFRICA')
    ,('04:03:02', 'Alan',  'ASIA'  )
    ,('05:03:02', 'Alan',  'NA'    )
    ,('07:27:48', 'Alan',  'ASIA'  )
    ,('01:09:10', 'Tom',   'EUROPE')
    ,('02:09:10', 'Tom',   'ASIA'  )
    ,('05:09:10', 'Tom',   'AUS'   )
    ,('12:09:10', 'Tom',   'AFRICA')
    ,('15:09:10', 'Tom',   'AUS'   )
    ,('05:06:58', 'Nancy', 'AFRICA')
    ,('08:06:58', 'Nancy', 'ASIA'  )
    ,('12:06:58', 'Nancy', 'NA'    )
    ,('15:06:58', 'Nancy', 'AFRICA')
    ,('17:33:06', 'Mike',  'EUROPE')
    )
    select nm.*
      from tbl_users nm
      join table
           (select max(n2.time) mtime
                 , max(n2.name) mname
              from tbl_users n2
             where n2.name = nm.name) tt
       On  tt.mtime = nm.time
       and tt.mname = nm.name
    ;
    ------------------------------------------------------------------------------
    
    TIME     NAME  LOCATION
    -------- ----- --------
    07:27:48 Alan  ASIA    
    15:09:10 Tom   AUS     
    15:06:58 Nancy AFRICA  
    17:33:06 Mike  EUROPE  
    
      4 record(s) selected.

  7. #7
    Join Date
    Jul 2009
    Posts
    150
    I beleive we are looking for maximum by location, not by name...

    But anyway I gave you the right idea how to do this.

    With the little change you have the working query.

    Kara Sw., NY

  8. #8
    Join Date
    Jul 2009
    Posts
    150
    If you want to have the righ query, it has to be following:

    select nm.*
    from
    tbl_users nm
    join table
    (select max(n2.time) as mtime, nm.name as mname
    from tbl_users n2
    where n2.name = nm.name
    ) tt
    On
    tt.mtime = nm.time
    and
    tt.mname = nm.name
    ;
    By, Kara Sw, NY
    Last edited by DB2Plus; 07-19-09 at 11:54.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that it might be better for me to ignore you,
    because you didn't understand OP's requirement and you didn't know well at SQL.

    Or, do you want to discuss another problem?

    OP's requirement is:
    I want the following as output from a select statement
    Time Name Location
    ---------- ------ --------
    07:27:48 Alan ASIA
    15:09:10 Tom AUS
    15:06:58 Nancy AFRICA
    17:33:06 Mike EUROPE
    and I showed the result of your query was different from OP's requirement.

    Your SQL has syntax error.
    1) You can't refer mname(which was named in SELECT clause) in WHERE clause.
    2) "mn" in "mn.*" was not defined anyware.
    select mn.*
    from
    tbl_users nm
    join table
    (select max(n2.time) as mtime, nm.name as mname
    from tbl_users n2
    where n2.mname = nm.name
    ) tt
    On
    tt.mtime = nm.time
    and
    tt.mname = nm.name
    ;

  10. #10
    Join Date
    Jul 2009
    Posts
    150
    Thinking before posting !

Posting Permissions

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