Re: select problem

From: Ed Prochak <edprochak_at_magicinterface.com>
Date: Tue, 11 Jun 2002 04:34:35 GMT
Message-ID: <3D05AA4F.F134EACE@magicinterface.com>

jim agans wrote:
>
> SELECT
> NAME,rowid,
> MAX(PAYROLL_YEAR||', '||PAY_PERIOD||' ,'|| BASESAL )
> FROM DAILYDBA.EMPINFO_AGENTS
> GROUP BY name, rowid;
>
> NAME ROWID
> MAX(PAYROLL_YEAR||','||PAY_PER
> ------------------------------ ------------------
> --------------------------------------------------
> AGANS, JAMES W AAAIjkACQAAABlAAAD 2001, 01 ,16.3
> AGANS, JAMES W AAAIjkACQAAABlAAAG 2002, 01 ,17.3
> JOHNSON, JOHN E AAAIjkACQAAABlAAAA 2002, 06 ,17.3
> JOHNSON, JOHN E AAAIjkACQAAABlAAAC 2001, 18 ,16.3
> JOHNSON, JOHN E AAAIjkACQAAABlAAAF 2002, 18 ,17.3
> SMITH, STRAPPIN, STEVE AAAIjkACQAAABlAAAE 2001, 05 ,17.3
> SMITH, STRAPPIN, STEVE AAAIjkACQAAABlAAAH 2002, 05 ,16.3
>
> 7 rows selected
>
> hi...this is what I have but its not really what I want . Im afriad I
> ve reached an impasse.
>
> What I need to do is get the latest PAYROLL_YEAR and PAY_PER for a
> given name. ex:
>
> for AGANS, JAMES W has 2 recs. I need the one for this name that has
> the 2002
> in the PAYROLL_YEAR field and 01 in the PAY_PERIOD field . This is the
> last record inserted for AGANS, JAMES W
>
> for JOHNSON, JOHN E I need to get the record the one w/ 2002
> PAYROLL_YEAR field and 06 PAY_PERIOD field. Once again the last
> inserted.
>
> any help ???
> by the way i need to put this in a cursor in order to update a field
> in this table.
>
> the field Ill call 'y' isnt in the sql yet ...I havent figured it out
> that far.
> thanks
>
> jim

get rid of the rowid. You shouldn't need it.

But if you must have it, consider that your key is the combined "name, PAYROLL_YEAR, PAY_PERIOD, BASESAL" fields. Using them you can find the records exactly. IOW, you original query could be written:

select AGENTS.name, AGENTS.rowid, MAXDATA from DAILYDBA.EMPINFO_AGENTS AGENTS,
  (select name,

          MAX(PAYROLL_YEAR||', '||PAY_PERIOD||' ,'|| BASESAL) as MAXDATA
           FROM DAILYDBA.EMPINFO_AGENTS
          GROUP BY name ) summary_view

where AGENTS.name = summary_view.name
  and AGENTS.PAYROLL_YEAR||', '||AGENTS.PAY_PERIOD||' ,'||AGENTS.BASESAL

• summary_view.MAXDATA;

(note: untested.)

Final note: I am always VERY WARY of using rowid. It is truely like a pointer in some languages, it can be fast, but can get you into deep trouble easily. Use it if it fits the problem requirements, but in many cases you really don't NEED it.

HTH
  Ed

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com

Received on Mon Jun 10 2002 - 23:34:35 CDT