Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Trouble with max(date) query

Re: Trouble with max(date) query

From: Bob Badour <bbadour_at_golden.net>
Date: Thu, 11 Sep 2003 17:13:31 -0400
Message-ID: <JM68b.168$NW6.4117619@mantis.golden.net>


"melhayes" <webbcom_at_yahoo.com> wrote in message news:16a64606.0309110751.10cad7e1_at_posting.google.com...
> Hello,
>
> I have a payroll table in Oracle 817 and I've having problems with
> what looks to me should be a simple query.
>
> The table has payroll records from the present back to 1999 with let's
> say 2 fields, staffno varchar2(7) and pay_date date. I'd like a list
> of all staff who's most recent pay date is less than 01-AUG-2002. This
> looks dead easy but I can't get it to work...
>
> this gives me the proper max pay_date
>
> select max(pay_date)
> from tblPay
> where pay_date < to_date('01-AUG-2002','DD-MON-YYYY');

This query does not match the criterion you describe above. This query gives you the most recent pay date prior to August 1, 2002.

The equivalent to what you describe above is:

select

            staffno
from

            tblPay
having

            max(pay_date) < to_date('01-AUG-2002','DD-MON-YYYY') ; Received on Thu Sep 11 2003 - 16:13:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US