SQL Question

db2udbgirl · #1 (**permalink**) 07-13-09, 15:08

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

n_i · #2 (**permalink**) 07-13-09, 16:03

Use a correlated subquery:

Code:

select
...
from table t1
where t1.time = (select max(time) from table1 where user=t1.user)

ARWinner · #3 (**permalink**) 07-13-09, 16:05

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

tonkuma · #4 (**permalink**) 07-13-09, 23:02

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
;

DB2Plus · #5 (**permalink**) 07-18-09, 21:58

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.

tonkuma · #6 (**permalink**) 07-19-09, 08:36

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.

DB2Plus · #7 (**permalink**) 07-19-09, 09:01

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

DB2Plus · #8 (**permalink**) 07-19-09, 09:14

If you want to have the righ query, it has to be following:

Quote:

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

tonkuma · #9 (**permalink**) 07-19-09, 10:27

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:

Quote:

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.

Quote:

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
;

DB2Plus · #10 (**permalink**) 07-19-09, 10:50

Thinking before posting !

Internet Services

Web Development

Digital Marketing

Consumer Tech