Re: Most efficient way to create a view with current values

From: Bram Pouwelse <bram_at_pouwelse.com>
Date: Mon, 23 Jul 2007 21:29:10 +0200
Message-ID: <3c981$46a50186$53545461$920@cache110.multikabel.net>

sybrandb_at_hccnet.nl wrote:

> On Mon, 23 Jul 2007 21:11:21 +0200, Bram Pouwelse <bram_at_pouwelse.com>
> wrote:
> 

>> 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.

> 
> yes of course there is, not using analytical functions.
> Also I would contest the second inline view is correct.
> 
> select d.name,
>           d. code,
>          d.time,
>         d.status
> from status d
> where time=
> (select max(time) 
>  from status d1
>  where d1.id= d.id
> )
> 

Sybrand,

I don't get it this would only work if the device and status data are in the same table but they are not.

To get it clear there is a time independent device information table and a status table with multiple status records per device a day. Received on Mon Jul 23 2007 - 14:29:10 CDT