-----------------------------------------------------------
--       name last2reads.sql
--    version 0.1
--description demonstrates a gimmick with the lag ( ) and 
--            dense_rank ( ) functions
--   synopsis accepts a device ID
--      usage run this from an interactive SQL tool such 
--            as SQL*Plus, SQL Developer, TOAD
--      calls nothing
--  called by nothing
--   comments note how this is ordered and partitioned
--  copyright David Clement 2007
--   warranty None expressed or implied
-- created by David Clement
--       date 11 April 2007
-- changed by
--       date
-----------------------------------------------------------

select last_time_read, 
  prev_time_read,
  last_value_read,
  prev_value_read
from (
  select time_read last_time_read,
    value_read last_value_read,
    lag (time_read, 1) over (order by time_read) prev_time_read,
    lag (value_read, 1) over (order by time_read) prev_value_read,
    dense_rank ( ) over (partition by device_id order by time_read desc) rnk
  from realtime_device_reads
  where device_id = &1
  and valid_flg = true
) 
where rnk = 1
/