Oracle OLAP option works really well. It is truly a hidden jewel in the database, which not many people realize should be part of every Oracle-database-based BI and DW solution. This is not a new server. It has been around since early 1970s, so it is even older than Oracle database. Oracle bought it in 1995 and "embedded" it inside the database starting with version 9.2 of database.
The latest version is 11.2.0.2 (which I will highly recommend). The older version had some issues.
Some useful links are here
Some additional comments about using Oracle OLAP option in a BI/DW environment.
(1). All data/calculations reside inside Oracle database.
(2). Any calculation (or calculated measures) no matter how complex can be created inside the cubes with simple analytical syntax which automatically works at all levels of all dimensions.
(3). Dimensional calculations (or calculated members) can be either done by OBIEE's Calculated-Item functionality or "pushed" inside the database where more complex dimensional calculations can be handled very easily.
(4). Any types of hierarchies (level-based or parent-child) can be handled.
(5). Data is retrieved through SQL queries using OLAP_TABLE function or CUBE_TABLE function.
(6). Provide faster query response times for any adhoc report.
(7). If you are using Oracle's OBIEE (11.1.1.5 or future releases) then the generated queries are simple SELECT...FROM..WHERE kind of queries. Even with other reporting tools, the queries are still simple queries as there is not much to do on "relational" side.
(8). Depending on requirements, the following functionality can be easily provided: drill-thru to detail (from aggregated cubes to relational transactions) OR combining relational and cube data in the same reports.
(9). Uses Oracle database resources, hence scalable for faster cube builds as well as handling hundreds or thousands of users.
(10). Very extensive logging features are provided out of the box (for Oracle DBAs) to monitor the queries as well as cube builds. This is in addition to regular features that DBA use (like ASH, AWR, OEM etc.).
(11). No additional hardware or DBA required (as opposed to other OLAP servers).
(12). One cube can replace dozens and dozens of MVs and summary tables. Inside the cube, the data exists at all levels of all dimensions.
The elementary difference is that window functions are applied to all rows in a result set to compute additional columns after the rest of the result set has been determined. No row is dropped. They are available since PostgreSQL 8.4.
The LIMIT and OFFSET clauses of the SELECT command on the other hand do not compute additional columns. They just pick a certain "window" of rows from the result set (in cooperation with the ORDER BY clause) and discard the rest. Have been there like for ever.
While certain tasks can be tackled with either of these tools, they are very different in nature.
For your simple task
sorting data on date and then bring the latest data first
you don't need either of them. Just add:
ORDER BY date_col DESC
According to your comment, you would need:
SELECT col1, col2
FROM tbl
ORDER BY date_col DESC
LIMIT 100 -- 100 latest rows
OFFSET 0; -- just noise, but may be easier to code
Retrieve more:
...
LIMIT 100
OFFSET 100; -- next page of 100 rows ...
Be sure to have an index on date_col in either case!
Best Answer
You are mixing up Db2 (Db2 for Linux, Unix and Windows) and Db2 for z/OS.
Db2 9.7 is LUW (Linux, Unix and Windows) and FIST_VALUE is available on these OS'es.
Db2 for z/OS does not support FIRST_VALUE.