Re: Need help with query in Oracle 8.1.7

From: <dba_222_at_yahoo.com>
Date: Sat, 25 Oct 2008 00:49:33 -0700 (PDT)
Message-ID: <bb4fbb49-f334-4b40-87ce-0bf928f32f63@n1g2000prb.googlegroups.com>

Perhaps?

select FACID

, DEPTID
,max ( CENUPD )
,max( CENUPT  )
, max (CENCOUNT  )

from the_table
Group by FACID
, DEPTID or
, max ( CENUPD || CENUPT || CENCOUNT )
using the appropriate conversion functions?

HTH On Oct 24, 4:25 am, Brian K <mailregd..._at_gmail.com> wrote:
> 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 Sat Oct 25 2008 - 02:49:33 CDT