Microsoft Dynamics AX (Archived)

how to return 2nd last record from table with select statement

(0) Share

Report

Posted on by @rp@n

Dear All,

I have one requirement. I need to return 2^nd last record from table with select statement.

For example : I having 10 records.

I need 8^th record only.

Please give me more shed on this.

Thanks!

Arpan Sen

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Sort by

Suggested answer

Rohin 4,624 on at

Like (1)

Report
Copy link

Link copied!

Martin Suggested answer already here.

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Could you please provide some more information on your business requirement.

As per your mentioned details we can get 2nd last record by sorting records in descending order based on recId and get the 3rd record from loop(as suggested by Crispin) (or) else you can introduce a new field on your table and maintain numeric sequence while inserting the data and retrieve based on this number.

Thanks,

Chaitanya Golla

Was this reply helpful? Yes No
Suggested answer

PA-22040759-0 6,194 on at

Like (1)

Report
Copy link

Link copied!
Here is is crude suggestion, that shouldn't select more records than required from the database:

static void Job10(Args _args) { CustTable custTable; select noFetch custTable order by accountNum desc; next custTable; next custTable; next custTable; print custTable.AccountNum; pause; }

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,124 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
I wouldn't be that excited.

Will it really fetch less records? I don't think so - each of the record is fetched on next anyway. It's the same thing as while select does under the hood.

Is it easier to maintain? For example, what if you decided to get the fifth record instead of the third? Will it be readable and intent-revealing?

What if the query returns less records than the number of next statements? For example, if it returns nothing, you would be still calling next several times for no reason. Also, just taking the result of the last next without checking if it contains a values is dangerous.

My answer (linked above) was in a thread about AX 2009. In AX 2012, you can consider creating a computed column with ROW_NUMBER or RANK() and filter by it.

Was this reply helpful? Yes No
PA-22040759-0 6,194 on at

Like (0)

Report
Copy link

Link copied!

Sorry to be unclear on the "crude suggestion" part. This was just an idea and not intended to be the one and only true way to solve this problem.

Was this reply helpful? Yes No
@rp@n 56 on at

Like (0)

Report
Copy link

Link copied!

Thanks Palle,

As you said it's working fine.

Can you please elaborate me about "select noFetch custTable".

Why you used noFetch before Table?

Please give me more shed on this.

Thanks!
Arpan

Was this reply helpful? Yes No
Rohin 4,624 on at

Like (0)

Report
Copy link

Link copied!

Martin,

"In AX 2012, you can consider creating a computed column with ROW_NUMBER or RANK() and filter by it."

can you please elaborate it more??

Was this reply helpful? Yes No
Martin Dráb 239,124 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You didn't mention which part you have a problem with, so I'll assume you're not familiar with computed columns at all. In such a situation, you should use a search engine to find the relevant documentation (Walkthrough: Add a Computed Column to a View [AX 2012]) and other articles (e.g. my Subqueries in views, which even contains an example with ROW_NUMBER()).

Was this reply helpful? Yes No