Most efficient way to create a view with current values

From: Bram Pouwelse <bram_at_pouwelse.com>
Date: Mon, 23 Jul 2007 21:11:21 +0200
Message-ID: <9fe91$46a4fd59$53545461$27750@cache110.multikabel.net>

I have a table with Devices and a Status table and want to create a view   which returns the Info of the device and the last known status

The tables:
DEVICE
Id
Name
Code

STATUS
Time
Status
Device_id

The view
CURRENT_DEVICE_STATUS

Name | Code | Time | Status
d1   | 1234 | 13.44| x
d3   | 4833 | 12.54| s

The device table contains a few thousand devices and each device has a few status changes a day,

current view code:

CREATE OR REPLACE VIEW CURRENT_DEVICE_STATUS
(

   Name, Code, Time, Status
)
AS
(

   SELECT

     d.Name,
     d.Code,
     (SELECT max(Time) keep (dense_rank first order by Time desc) Time 
FROM status WHERE device_id= d.id),
     (SELECT max(Status) keep (dense_rank first order by Time desc) 

Status FROM status WHERE device_id= d.id)

   FROM Device d
);

Is there a more efficient way to create a view like this?

Bram. Received on Mon Jul 23 2007 - 14:11:21 CDT