Home » SQL & PL/SQL » SQL & PL/SQL » Last visit on location
Last visit on location [message #216297] Fri, 26 January 2007 04:15 Go to next message
SITO
Messages: 19
Registered: March 2005
Location: SWE
Junior Member
I need some help finding last location per group.. I've tried using the examples with highest salery and so on but haven't found what im looking for.

Below is the data availible.. The desired output is the last LOCATION1 visited for each GROUP1 based on TIME.

TIME            AREA      GROUP1 LOCATION1
--------------- ----- ---------- ----------
19:09:20        40       1597033 3001504901
19:11:03        40       1597033 3001405201
18:38:53        40       1597035 5511506401
18:40:27        40       1597035 3001304101
18:41:03        40       1597035 3001300701
18:42:24        40       1597035 3001005201
20:28:42        40       1597040 3001404701
20:31:35        40       1597040 3001401901
20:45:31        40       1597047 3001203001
19:41:34        40       1597051 5512202501
19:42:38        40       1597051 5512200601


Any ideas ?


Re: Last visit on location [message #216303 is a reply to message #216297] Fri, 26 January 2007 04:59 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
I hope I have understood your requirement.
See if below script helps you.

create table tab1 (TIME date,AREA number,GROUP1 number, LOCATION1 number)


insert into tab1 values (to_date('19:09:20','HH24:MI:SS'),40,1597033,3001504901);

insert into tab1 values (to_date('19:11:03','HH24:MI:SS'),40,1597033, 3001405201);
insert into tab1 values (to_date('18:38:53','HH24:MI:SS'),40,1597035, 5511506401);
insert into tab1 values (to_date('18:40:27','HH24:MI:SS'),40,1597035, 3001304101);
insert into tab1 values (to_date('18:41:03','HH24:MI:SS'),40,1597035, 3001300701);
insert into tab1 values (to_date('18:42:24','HH24:MI:SS'),40,1597035, 3001005201);
insert into tab1 values (to_date('20:28:42','HH24:MI:SS'),40,1597040, 3001404701);
insert into tab1 values (to_date('20:31:35','HH24:MI:SS'),40,1597040, 3001401901);
insert into tab1 values (to_date('20:45:31','HH24:MI:SS'),40,1597047, 3001203001);
insert into tab1 values (to_date('19:41:34','HH24:MI:SS'),40,1597051, 5512202501);
insert into tab1 values (to_date('19:42:38','HH24:MI:SS'),40,1597051, 5512200601);

select * from tab1;

select GROUP1,LOCATION1,Time Last_Time from tab1 a
where Time in (select max(time) Last_Time from tab1 b
where b.GROUP1 = a.GROUP1
group by GROUP1);

Output is -
group1 location1 last_time
------------------------------------------------
1597033 3001405201 19:11:03
1597035 3001005201 18:42:24
1597040 3001401901 20:31:35
1597047 3001203001 20:45:31
1597051 5512200601 19:42:38

Regards,
Anil Sinare
Re: Last visit on location [message #216305 is a reply to message #216303] Fri, 26 January 2007 05:08 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Another way to do the same:
SELECT t.group1, TO_CHAR(t.time, 'hh24:mi:ss') time, t.location1
FROM (SELECT group1, location1, time,
             rank() over (PARTITION BY group1 ORDER BY time DESC) rnk
      FROM tab1
     ) t
WHERE rnk = 1;
Re: Last visit on location [message #216308 is a reply to message #216305] Fri, 26 January 2007 05:51 Go to previous message
SITO
Messages: 19
Registered: March 2005
Location: SWE
Junior Member
Worked like a charm ! Only tried the 'littlefoot-solution' but thanx to the both of you !
Previous Topic: SP2-0552 Bind variable not declared ERROR
Next Topic: How to retrive only Duplicate rows from table-reg
Goto Forum:
  


Current Time: Sat Dec 03 20:17:48 CST 2016

Total time taken to generate the page: 0.09758 seconds