I have this merge statement:

MERGE INTO rank_history_new zrh
USING (SELECT v_id AS new_m_ticker, v_rec.z_rank_d AS new_rank
FROM dual) zrh2
ON (zrh.m_id = v_id AND rank = v_rec.z_rank_d)
WHEN NOT MATCHED THEN
INSERT (m_id, rank, time, add_del_flag)
VALUES (v_id, v_rec.z_rank_d, v_rank_date, v_add_del_flag);

It does not work 100%. Actually what I want is to compare it with the
LAST entry for the v_id. So, if I have the following. So if there
are 4 entries for a given ID, I want to compare it against the last
entry.

Can this be done? I'd like to use MERGE rather than having to do a
select, then a separate compare to decide if I need to
insert.....etc....

On Feb 2, 2:06*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:
What do you mean by 'last entry', since a heap table has no order?
Are these values sequenced, dated, both? Is there some definitive way
to order these results to ensure you're comparing against what you
consider the 'last entry'?



David Fitzjarrell

On Feb 2, 3:12*pm, ddf <orat... (AT) msn (DOT) com> wrote:
David,

In this case, rank_history_new has several records for each ID. There
is also a DATE on those records. When performing the MERGE, I want to
compare against the record with the MAX date for that ID.

ID DATE
-----------------
A 6/3/08
A 9/7/08
A 1/5/09 <-- Use this one in the MERGE statement.

So again, back to the merge statement, I am comparing 'rank' values,
but I want to perform that compare on the record with the MAX date for
that ID.

Does this help a bit in my explanation?

On Feb 3, 7:59*am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
Can you provide DDL for the tables and some sample data?


David Fitzjarrell