Select first five, select in random order

(0) Share

Report

Posted on by @rp@n

Dear All,

Suppose i have one table say StudentDetails.
It's having 30 records with Student ID and Name.
Student ID : S01,S02,S03,S04,S05,S06,S07,S08,S09 etc

I have 2 queries

1. I want to return first 5 records only.
Is there any function in AX OR I have to write while select with for loop?

2. I want to return randomly Student ID like S02,S05,S08 with using while select.
How to acheive this.
Could you please give more shed on this.

Thanks!

Arpan

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

1) You could use a counter in a while select and use if (counter == 5) break;

2) There is no such thing for a relational database as "random", it is always ordered by some columns (at least by cluster index).

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

Like (0)

Report

Thanks Vilmos

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,882 Most Valuable Professional on at

Like (0)

Report

If you consult the select statement syntax (as you always should if you're in doubt), you'll find that it supports firstOnly and firstOnly10, but no firstOnly5. Therefore you can use a while select with firstOnly10 and break the loop as explained by Vilmos.

Regarding random order, consider getting IDs as usual, putting them to an array and using Random class to get random indexes. Alternatively (especially if there are many records), you can create a view with a computed column based on RAND() function.

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

Like (0)

Report

Thanks Martin

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi @rp@n ,

please look into my blog it is may useful to you future.

http://axsuresh.blogspot.in/2016_05_01_archive.html

Thanks,

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

Like (0)

Report

Thanks Suresh

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

Like (0)

Report

The link shows just "No posts" message to me. Could fix the link and provide a description of what's there, so people can find the solution here even if the linked site doesn't work?

Was this reply helpful? Yes No