Hi Clay!

Try this:

UPDATE NOTES N1
SET ADMIT_ORDINAL = SELECT COUNT(*) FROM NOTES N2
WHERE N2.CLIENT_ID = N1.CLIENT_ID
AND N2.ENTRY_DATE <= N1.ENTRY_DATE

This should help,
Set

--- In firebird-support@yahoogroups.com, "Clay Shannon" wrote:
> I have tables of data that have been pumped over from roughly
> corresponding Access tables.
>
> In one type of table, Notes regarding a patient's admissions are
> kept. One column holds the patient's id, and another the "admit
> ordinal." Unfortunately, the original Access tables did not have the
> admit ordinal column, but they did have an "Entry Date" column for
> when the note regarding the patient was entered.
>
> I want to be able to update the Firebird Notes tables by inserting
> the appropriate value in the Admit Ordinal column. IOW, this:
>
>
> CLIENT_ID ENTRY_DATE ADMIT_ORDINAL
> 111 1/1/2001 <null>
> 111 4/5/2001 <null>
> 222 3/3/2002 <null>
> 333 7/4/2003 <null>
> 333 7/5/2004 <null>
> 333 7/6/2005 <null>
>
> ...
>
> should become this:
>
> CLIENT_ID ENTRY_DATE ADMIT_ORDINAL
> 111 1/1/2001 1
> 111 4/5/2001 2
> 222 3/3/2002 1
> 333 7/4/2003 1
> 333 7/5/2004 2
> 333 7/6/2005 3
>
> ...
>
> Does anybody know how to accomplish this with a SQL Update st8ment?
>
> Clay Shannon,