Need help with query in Oracle 8.1.7

From: Brian K <mailregdump_at_gmail.com>
Date: Fri, 24 Oct 2008 11:25:49 GMT
Message-Id: <pan.2008.10.24.05.46.49.859000@gmail.com>


I have an older database system which I have inherited and need to extract some data on.

I am having trouble with a query.

The query in question runs against a table with five columns:

Primary key:

FACID     CHAR(10)
DEPTID	  CHAR(5)

Additional columns:
CENUPD DATE
CENUPT CHAR(8)
CENCOUNT CHAR(10) There are a few hundred thousand total rows in this table.

The table is used to store patient headcount, and is grouped by facid/deptid. CENUPD is a date field storing the DD-MM-YY of the census, and CENUPT stores the HH24:MI:SS of the census in a text format.

The FACID is a facility ID, the DEPTID is a department ID within a facility.

The challenge I have is to craft a query with PL/SQL to roll these values up in such a way that I return ONLY the MOST RECENT cenupd, cenupt, and cencount values for each distinct combination of FACID and DEPTID. Out of the hundred thousand total rows I should have a few hundred combinations of FACID and DEPTID.

I appreciate your help, and please do not hesitate to ask for more information. Received on Fri Oct 24 2008 - 06:25:49 CDT

Original text of this message