Get latest data record

Sometimes I have a request to search within a table find out for example when a server delivered his data last time. Here some sample data. As you maybe recognize for some days some servers have not delivered any data:

[table]
Timestamp,Server,Metric,Value
15-NOV-2015,Server2,CPU_Util,30

15-NOV-2015,Server2,RAM_Util,60

14-NOV-2015,Server1,RAM_Util,70

13-NOV-2015,Server1,CPU_Util,23
13-NOV-2015,Server2,RAM_Util,60

12-NOV-2015,Server2,CPU_Util,10
12-NOV-2015,Server2,RAM_Util,43

[/table]

With following SQL which use analytic function, you’ll find the latest data for each server

SELECT distinct
    SERVER,
    MERTIC,
    max(TIMESTAMP) over (partition by SERVER||METRIC) last_data
FROM <TABLE>

or use the “normal” way with group aggregation

SELECT 
   SERVER,
   METRIC,
   max(SSUB_COLLECTION_TIME)  last_data
FROM <Table>
GROUP BY SSUB_SID, SSUB_POOL_NAME

The output should be as follows:

[table]
Server,Metric,Timestamp
Server2,CPU_Util,15-NOV-2015

Server2,RAM_Util,15-NOV-2015

Server1,CPU_Util,13-NOV-2015

Server1,RAM_Util,14-NOV-2015

[/table]

Please take care that the statements above will do a full table scan, so it is wise to use some indexes to increase the query performance. I would suggest an index on the TIMESTAMP column to limit the number of rows.

CREATE INDEX IDX_<TABLE>_TIMESTAMP ON <Table> ( TIMESTAMP );

SELECT distinct
    SERVER,
    MERTIC,
    max(TIMESTAMP) over (partition by SERVER||METRIC) last_data
FROM <TABLE>
WHERE TIMESTAMP > SYSDATE-32

Source: Oracle Analytic Functions