SQL to find titles of books with the highest circulations

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

Description:
What SQL can be used to find the titles of books with the highest circulations?

Resolution:
[Note: This uses the adm z103 to read the z13 from the bib library.]

select
title||'\'||
call_no||'\'||
qty
from
(select count(z30_barcode) as qty, z30_call_no as call_no, z13_title as title
from z36h, z30, law01.z13, z103
where z30_item_process_status is null and z30_collection = 'MAIN' and
substr(z30_rec_key, 1,9) = substr(z36H_rec_key, 1,9) and
substr(z36H_rec_key, 1,9) = substr(z13_rec_key, 1,9) and
z36H_loan_date >= '20050101' and
z36H_loan_date <= '20101130' and
z30_barcode = z30_barcode and
z30_no_loans > 50 and
z36H_loan_date is not null and
Z13_REC_KEY = substr (Z103_REC_KEY_1,6,9) and
substr (Z103_REC_KEY_1,1,5) = 'ABC01' and
substr (Z103_REC_KEY,6,9) = substr (Z30_REC_KEY,1,9)
group by z30_barcode, z13_title, z30_call_no
order by z30_barcode, z13_title, z30_call_no
)
where qty >=50;

• Article last edited: 10/8/2013