Think along these lines. What is it that defines the maximum date? Answer:
the date which has no entry in the table which is greater. I.e " not other
dates in the table are greater than me, therefor, I am the greatest date.
select case_nbr
from table_a ta
where member_nbr = 'xxxxxx' and
plan_type = 'xxxxxx' and
not exists (select 1 from table_a tb where tb.member_nbr =
ta.member_nbr and
ta.plan_type = tb.plan_type and ta.modify_date >
tb.modify_date);
If the inner select finds a hit, then the outer select rejects it's current
row.
Mike
On Fri, Jan 6, 2012 at 7:24 AM, imalukegal <dgre...@centene.com> wrote:
> I am trying to select the most current record for a member on a
> table. The only way to do this is by using a field called
> modify_date. I've tried several things, but nothing has worked. Any
> suggestions?
>
> select case_nbr
> from table_a
> where member_nbr = 'xxxxxx' and
> plan_type = 'xxxxxx' and
> max(modify_date);
>
> Thanks for the help.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
