LAST_VALUE analytic functions

Oracle analytic functions are great! There is a few of them which I like particularly and would like to post here. I always need to check tables for the last entry of a record.

LAST_VALUE

The LAST_VALUE analytic function returns the last_value of an ordered set of values. Think of a table with data like this:

SQL> select * from tab1;

        ID     ENDEKS ISIM
---------- ---------- ----------
         1       1001 AA
         1       1002 CV
         1       1003 FC
         2       1001 AA
         3       1001 KK
         3       1000 LL
         2       1002 ZZ
         2       1010 MM

Suppose, all you wanted to see is the last entry for each ID at any point in time, then an Oracle Analytics SQL Query like this would do.
SQL> select x.id,max(endeks), isim from
(
SELECT ID,
last_value(endeks) over(PARTITION BY ID order by endeks asc) endeks,
last_value(isim) over(PARTITION BY Id ) isim
FROM tab1
order by 1 , 2
) X
group by x.id, isim
order by 1
/

     ID MAX(ENDEKS) ISIM
---------- ----------- ----------
         1        1003 FC
         2        1010 MM
         3        1001 KK

Same answer with MAX and Oracle Analytics

It is possible to get the same answer with Oracle Analytics and the MAX function. If your table is very big, use this one as it is much more efficient as it goes through your table only once. Use Autotrace for both statements to see the difference.
SQL> 1 select id, endeks, isim from
(
select a.*, max(endeks) over (partition by id) last_message
from tab1 a
)
where endeks = last_message
/

        ID     ENDEKS ISIM
---------- ---------- ----------
         1       1003 FC
         2       1010 MM
         3       1001 KK