Most recent loan date for items

• Article Type: General
• Product: Aleph
• Product Version: 19.01

Description:
For items that have been checked out multiple times, there is more than one z36h entry. If we have the following, how do we get the record with the most recent loan date only using SQL:

Barcode Title Loans Loan Date
39072021485275 Normal infant reflexes and development [videorecording] / the Hospital for Sick Children, Department 44 20070121 --> only want this on report
39072021485275 Normal infant reflexes and development [videorecording] / the Hospital for Sick Children, Department 44 20061108
39072021485275 Normal infant reflexes and development [videorecording] / the Hospital for Sick Children, Department 44 20060908

Resolution:
[From site:] We found that "group by" needed to be added. This worked:

SQL> select z30_barcode, z13_title, z30_no_loans, max(z36h_loan_date)
from buf01.z13, buf50.z30, buf50.z36h, buf50.z103
where
z30_sub_library = 'BUFHM' and
z13_year <= 1990 and
z36h_rec_key = z30_rec_key and
(
Z103_LKR_TYPE = 'ADM'
and
upper(substr(Z103_REC_KEY,1,5)) = 'BUF50'
and
upper(Z103_LKR_LIBRARY) = 'BUF01'
and
substr(Z103_REC_KEY,6,9) = substr(Z30_REC_KEY,1,9)
and
Z103_LKR_DOC_NUMBER = Z13_REC_KEY
)
group by z30_barcode, z13_title, z30_no_loans, z30_date_last_return
order by z30_barcode;

• Article last edited: 10/8/2013