I have a database with a statistics table that has a record for each time an
object on my server is accessed. Its like an audit trail, and contains the
IP address that accessed it, etc. It has a foreign key reference to the
object that the access was for (ie. It’s a many to one relationship).

I need to return the top 10 accessed objects for a given month. I am
developing a stored procedure to do this, and I have it working (well
sorta)...

I have it so that it returns the list of objects accessed based on the
number of rows in this table within a certain date range. I can limit the
returned values by using the DISTINCT and Limit capabilities of Firebird so
that I get 10 records, however I need it to return the following values:

OBJECT_ID
OBJECT_NAME
NO_ACCESSES

I have no problem cross referencing the ID of the statistics table's foreign
key reference to the object table, and returning information from the object
table (ie. Object_ID and Object_Name). However I want to also return the
number of stats records per object as well.

I can't see any way of doing this without using the COUNT function, but
according the Helen's incredible tome of knowledge on Firebird, it’s a big
NO NO to use the COUNT function. Without returning every statistics row to
my PHP application that is going to format and display the values, what
other way is there to return the number of rows that meet a criteria in a
select statement in Firebird?

Here's my stored procedure so far (it’s a work in progress)...

begin
/* Returns the top 10 collections for the current month */
/* Get the current month for the search */
V_TEMP_DATE = CAST('NOW' AS DATE);
V_CUR_MONTH = EXTRACT(MONTH FROM V_TEMP_DATE);

/* Now find the top 10 statistics for visible collections */
for
select
(select C.COL_NO from COLLECTION C where
C.COL_NO = (select SND.COL_NO from SOUND SND where SND.SOUND_NO =
ST.ST_SOUNDNO)),
(select C.COL_NAME from COLLECTION C where
C.COL_NO = (select SND.COL_NO from SOUND SND where SND.SOUND_NO =
ST.ST_SOUNDNO))
from stats ST
WHERE CAST(EXTRACT(MONTH FROM ST.st_when) AS INTEGER) = :v_cur_month
INTO
R_COL_NO,
R_COL_NAME
do
begin
suspend;
end
end

I'm not limiting the number of returned rows or sorting them on popularity
just yet, but I want to add a COUNT statistic so that I know the number of
statistics records that were found for each collection.

Any ideas?

Myles


============================
Myles Wakeham
Director of Engineering
Tech Solutions US, Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440
www.techsol.org