Re: Need help with query in Oracle 8.1.7

From: Ed Prochak <edprochak_at_gmail.com>
Date: Fri, 24 Oct 2008 06:11:17 -0700 (PDT)
Message-ID: <5d13b969-44a5-41bd-9be6-e349f4c61393@79g2000hsk.googlegroups.com>


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 Received on Fri Oct 24 2008 - 08:11:17 CDT

Original text of this message