Re: Need help with query in Oracle 8.1.7

From: ddf <oratune_at_msn.com>
Date: Fri, 24 Oct 2008 06:18:01 -0700 (PDT)
Message-ID: <e8c2dc18-6703-4ec3-bf19-8074c48d832f@2g2000hsn.googlegroups.com>


On Oct 24, 8:11 am, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Oct 24, 7:23 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
>
>
>
>
>
> > "gazzag" <gar..._at_jamms.org> schreef in berichtnews:5b6f8400-2fb8-4536-bd5f-06f395bc1373_at_t65g2000hsf.googlegroups.com...
> > On 24 Oct, 12:25, 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.
>
> > DDL to create the table plus some DML to populate it with some sample
> > data will get you help quicker.
>
> > HTH
>
> > -g
>
> > ==============================================
> > Wouldn't a select max( ) group by
>
> > be enough to do the job?
>
> > Shakespeare
>
> That's what I was thinking. Assuming the requirement "ONLY the MOST
> RECENT" means by date and time, he just needs to put the character
> time back into the date. It's simple enough to do.
>   Ed- Hide quoted text -
>
> - Show quoted text -

That presumes it's missing; it may not be as it may be merely a display format issue. We don't have enough information to deduce either way.

David Fitzjarrell Received on Fri Oct 24 2008 - 08:18:01 CDT

Original text of this message