Order By Derived Field

[funscrapn]

Hi,
I have created a report that pulls the history rows of the JOB table. Now I need an Order By or Row Number derived field. I have been unable to create this. Any suggestions?

The background on this report is that I only want the last 5 rows from the history of the JOB table. Unfortantly due to the amount of information in the system and the fact that everyone has different effective dates and such it seems like the above is the way to go. This way, once I have the above I can place a selection criteria in to pull rows that are less than 5.
Thanks!

 

[obiwaugh]

This derived field will rank the rows...then you select which ones you want. I'm assuming that you've added the JOB table to the report and given it an alias of 'J1'

(SELECT COUNT(*) FROM DEMOV4.dbo.PS_JOB J2
WHERE J1.EMPLID = J2.EMPLID
AND (J1.EFFDT < J2.EFFDT OR
(J1.EFFDT = J2.EFFDT AND J1.EFFSEQ < J2.EFFSEQ)))

 

[christimess]

In further review I think the other part of the problem is that I have to translate (for example) the action reason. Since the JOB table effective date is set to none I have to create a selection criteria to pull the top of stack for the action reason table.
The selection criteria I created is as follows:
data field A1.EFFDT is equal to formula
(SELECT MAX (ACTION2.EFFDT) FROM DEMO.PS_ACTN_REASON_TBL ACTION 2 WHERE A1.EFFDT = ACTION2.EFFDT)
(A1 is my alias for the action reason table.)
Even though I have the above I am still getting all of the rows from the action reason table.

 

[obiwaugh]

I responded to this issue in your other post.