SQL & PL/SQL

Hi friends
I am using oracle 11g

I have at doubt regarding the following.

create table Device(Did char(20),Dname char(20),Datetime char(40),Val char(20));

insert into Device values('1','ABC','06/13/2012 18:00','400');
insert into Device values('1','abc','06/13/2012 18:05','600');
.
.
.
insert into Device values('1','abc','06/13/2012 18:55','600');
insert into Device values('1','abc','06/13/2012 19:00','-32768');
insert into Device values('1','abc','06/13/2012 19:05','800');
insert into Device values('1','abc','06/13/2012 19:10','600');
insert into Device values('1','abc','06/13/2012 19:15','900');
.
.
.
insert into Device values('1','abc','06/13/2012 19:55','1100');
insert into Device values('1','abc','06/13/2012 20:00','-32768');
insert into Device values('1','abc','06/13/2012 20:05','-32768');

Like this I am inserting data into table for every 5 minutes Here i need the result like

output:
Dname 18:00 19:00 20:00

abc 400 -32768 -32768

to retrieve this result i am using decode function

SELECT Dname,
MAX(DECODE ( rn , 1,val )) h1,
MAX(DECODE ( rn , 2, val )) h2,
FROM
(SELECT Dname,Datetime,row_number() OVER
(partition by Dname order by datetime asc) rn FROM Device
where substr(datetime,15,2)='00' group by Dname.

According to above data expected result is

Dname 18:00 19:00 20:00

abc 400 600(or)800 1100

This means I dont want to display negative values instead of that values i want to show previous or next value.

Edited by: 913672 on Jul 2, 2012 3:44 AM