Skip to main content
ExLibris
  • Subscribe by RSS
    • Back
    Aleph
    Ex Libris Knowledge Center

    SQL FIRST / LAST functions

    1. Last updated
    2. Save as PDF
    • Article Type: General
    • Product: Aleph
    • Product Version: 20

    Description:
    How can the SQL FIRST and LAST functions be used?

    Resolution:
    [From Mike Rogers, U. Tennessee - Knoxville, on the Global Aleph Users list;]

    I thought I'd pass along this tidbit to those of you who use SQL in Aleph. It is the FIRST/LAST function and it can be used in a variety of ways. The use I found for it was in reporting, if you only want to display the first and last items of a data set.

    For example, we started an inventory project in Aleph using the "Inventory Marking" tool in the Cataloging module. Forty-six books were inventoried on the first test of our Juvenile fiction collection. Our staff wanted a report listing the call number range of the books that had been inventoried for a particular day or range of dates, along with a total count of books inventoried for each day.

    I knew that items scanned by Inventory Marking received an updated date in Z30_ON_SHELF_DATE, but couldn't figure out the rest of the SQL syntax. I started messing around with FIRST/LAST and then hit on this SQL query which gives us exactly what we need:


    COLUMN TO_CHAR(TO_DATE(Z30_ON_SHELF_DATE,'YYYYMMDD'),'YYYY/MM/DD') HEA 'InvDate'
    COLUMN MIN(SUBSTR(Z30_CALL_NO,4,25))KEEP(DENSE_RANKFIRSTORDERBY(Z30_CALL_NO_KEY)) FOR A23 HEA 'FromCallNo'
    COLUMN MAX(SUBSTR(Z30_CALL_NO,4,25))KEEP(DENSE_RANKLASTORDERBY(Z30_CALL_NO_KEY)) FOR A23 HEA 'ToCallNo'
    COLUMN COUNT(*) HEA 'ItemCount'

    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */
    TO_CHAR(TO_DATE(Z30_ON_SHELF_DATE,'YYYYMMDD'),'YYYY/MM/DD'),
    MIN(SUBSTR(Z30_CALL_NO,4,25)) KEEP (DENSE_RANK FIRST ORDER BY (Z30_CALL_NO_KEY)),
    MAX(SUBSTR(Z30_CALL_NO,4,25)) KEEP (DENSE_RANK LAST ORDER BY (Z30_CALL_NO_KEY)),
    COUNT(*)
    FROM UTK50.Z30
    WHERE Z30_COLLECTION = 'JUV'
    AND Z30_ON_SHELF_DATE = '20120215'
    GROUP BY TO_CHAR(TO_DATE(Z30_ON_SHELF_DATE,'YYYYMMDD'),'YYYY/MM/DD');


    The output from the query looks like this:

    InvDate FromCallNo ToCallNo ItemCount
    ---------- ----------------------- ----------------------- ----------
    2012/02/15 FA129fi FA291fo 46

    So on February 15th, the first call number scanned was FA129fi and the last call number scanned was FA291fo. ItemCount gives the total number of items scanned.

    I can potentially see some other uses for this function, too.

    • Article last edited: 10/8/2013