I think that your description of requirement is incomplete
and your example is too little.
For example:
a) how to know today?
If used CURRENT_DATE special register, your data doesn't include today's data.
b) If a data(name) exists yesterday, but does not exists today,
how to do?
Simply ignore it?
Anyhow, here is a sample query to get the same result from your example data.
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
sample_data(name , updated_time) AS (
VALUES
( 'appgroup_mem_sz' , '2011-12-01-00.00.00.000000' )
, ( 'auto_del_rec_obj' , '2011-12-01-00.00.00.000000' )
, ( 'appgroup_mem_sz' , '2011-12-02-06.32.32.375636' )
, ( 'auto_del_rec_obj' , '2011-12-02-06.32.32.375636' )
, ( 'auto_prof_upd' , '2011-12-02-06.32.32.375636' )
)
SELECT t.*
FROM sample_data t
WHERE DATE(updated_time) = /*today*/ DATE('2011-12-02')
AND NOT EXISTS
(SELECT 0
FROM sample_data ne
WHERE ne.name = t.name
AND DATE(ne.updated_time) = DATE(t.updated_time) - 1 DAY
/* yesterday = today - 1 day */
)
;
------------------------------------------------------------------------------
NAME UPDATED_TIME
---------------- --------------------------
auto_prof_upd 2011-12-02-06.32.32.375636
1 record(s) selected.
dBforums
>
Comparison of rows.
12-14-11, 06:30
Linear Mode
