×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Order By Derived Field

Order By Derived Field

Order By Derived Field

(OP)
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!

RE: Order By Derived Field

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)))

RE: Order By Derived Field

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.

RE: Order By Derived Field

I responded to this issue in your other post.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close