Hello.

What will be the best way to print the last 10 records from a table with 1M records.

Tried using LAST with COUNTER and SORT. Since I have a large table, this technique is taking a really long time.

Any assistance will be greatly appreciated.

Thank you.

This message has been edited. Last edited by: Kerry,

Is the table an RDBMS table? Does the "last" mean the highest values based on a sort (hopefully indexed) column?

This might be of help:

TABLE FILE BASEL_TIME_D
SUM MIN.TIME_DIM_KEY
BY HIGHEST TIME_DIM_KEY
WHERE READLIMIT EQ 10
END

The generated SQL:

SELECT
T1.TIME_DIM_KEY,
MIN(T1.TIME_DIM_KEY)
FROM
BASEL.TIME_D T1
GROUP BY T1.TIME_DIM_KEY
ORDER BY
T1.TIME_DIM_KEY DESC
FETCH FIRST 10 ROWS ONLY FOR FETCH ONLY;

The combination of "DESC" (descending) and "FETCH FIRST 10 ROWS" shpuld give you the 10 highest rows based on the sort column.

Consider the following (WHERE RECORDLIMIT EQ # or BY HIGHEST # (field)), as to whether you want duplicate TIMESTAMPS (if possible in your DB) or not.

TABLE FILE MYFILE
BY HIGHEST 5 TIMESTAMP
END

-* Note that only 1 record of: 20:37:38
-* TIMESTAMP 
-* 2009/07/25 20:39:32 
-* 2009/07/25 20:37:38 
-* 2009/07/19 10:39:48 
-* 2009/07/19 10:37:59 
-* 2009/07/19 10:33:09 

TABLE FILE TFNEXPENSES
PRINT TIMESTAMP 
-* SUM could eliminate dups as well
BY HIGHEST TIMESTAMP
WHERE RECORDLIMIT EQ 5
END

-* Note that there are 2 records for: 20:37:38
-* TIMESTAMP           TIMESTAMP 
-* 2009/07/25 20:39:32 2009/07/25 20:39:32 
-* 2009/07/25 20:37:38 2009/07/25 20:37:38 
-* 2009/07/25 20:37:38 
-* 2009/07/19 10:39:48 2009/07/19 10:39:48 
-* 2009/07/19 10:37:59 2009/07/19 10:37:59 

I enclosed the results within the commented code. I hope this helps.

Or, perhaps, you can consider WHERE READLIMIT EQ #, if the DB is in presorted descending order.

The DB does not need to be presorted in descending order to use WHERE READLIMIT EQ # - the sort is done first and then only # rows are returned to WebFOCUS.

When using BY HIGHEST #, the sort is done and all rows are returned to WebFOCUS which selects the first # returned rows for the report - quite a bit less efficient than the other method.

Thank you for responding.

I have a bit of a challenge with the suggested methods.

The table in question is a FOC repository - BOTLOG2 which does not have a timestamp or any field I can use as index. I think RC stopped recording to the BOTLOG2 at some point and that's why I want to retrieve the last 10 records.

Thank you for taking the time.

If you join BOTLOG to BOTLOG2, you will have a datetime stamp to look at. It is in GMT and there are lots of posts on it.

The other thing you can look at is the STAT_BT_NAME field. It is assigned in order so that the next one is higher than the previous one. That would allow you to do the BY HIGHEST 10 STAT_BT_NAME. Check it out.

I'm not sure if you can do this in less steps, but here's an example:

-*-- Display last 10 rows of a non-indexed FOCUS database

SET COUNTWIDTH = ON
TABLE FILE BOTLOG2 
COUNT STAT_BT_NAME
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

-READ H001 &MAXREC.I9.

-TYPE MAXREC &MAXREC

TABLE FILE BOTLOG2
LIST *
ON TABLE HOLD AS H002 FORMAT ALPHA
END
-RUN

TABLE FILE H002
PRINT *
WHERE E01 GT (&MAXREC - 10)
END
-RUN

This determines the number of rows in the FOCUS database. Then it lists all the rows, creating a counter column called list. Then it prints the last 10 rows.

- SET COUNTWIDTH changes the default size of the COUNT and LIST generated columns from I5 to I9.
- E01 is used because the column name "LIST" (generated by the previous LIST command) cannot be used as the filter column.

BOTLO2 is probably a good candidate for a cleanup, using CREATE or REORG - I'm not sure if there is a Report Caster utility that cleans up this log file.

Ginny, I forgot about joining to BOTLOG.

As for STAT_BY_NAME, they appear to be randomly named (eg: J14b1ka5vk29) and you can have dozens of rows for each STAT_BY_NAME, so BY HIGHEST 10 STAT_BY_NAME will probably not give you the last 10 rows in BOTLOG2. Those date stamps, START_STAMP and END_STAMP (e.g. 00000000000000000001246147201071) don't look like the GMT I know of, is some kind of conversion required? I recall reading a few posts on this subject.

They are randomly generated but each appears to be higher than the rest. Maybe I'm wrong but I've worked with them with that assumption.

So he could get the last one and find out how many rows there were for that one. Then he'd know how to get the last 10.

Thank you for all your posts.

I've combined a couple of the suggested techniques to list the last 20 records from BOTLOG2 which I joined with BOTLOG to display SMARTDATE.