Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Trouble with max(date) query
"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