web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / ORDER By in x++ sql st...
Finance | Project Operations, Human Resources, ...
Suggested answer

ORDER By in x++ sql statement

(0) ShareShare
ReportReport
Posted on by CRSW 625

Hi I wrote an SQL Statement in x++. This Statement should return the firstonly record ordered ascending by the inventsizeid 

Now inventsizeid is a string value and so return the wrong value. 

e.g. I do have records with inventsizeid 7.5  8.5 9 9.5 and 10

the Statement should return 7.5 but returns 10 as lowest value….and 9.5 as highest.

How can I get the correct results? 
Is it possible to use conversion  str2num in order by Expression?

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 236,394 Most Valuable Professional on at
    RE: ORDER By in x++ sql statement

    Can you show us your code, please?

  • CRSW Profile Picture
    CRSW 625 on at
    RE: ORDER By in x++ sql statement

    select firstOnly1 InventSizeId, OrderId, ExternalItemId,PriceGtz, PriceVend, LinePercentGtz, LinePercentVend from sizeLine
                order by sizeLine.InventSizeId asc
            where sizeLine.OrderId         == tmpVoucherLines.OrderId
                && sizeLine.ExternalItemId       == tmpVoucherLines.ExternalItemId
                && sizeLine.PurchPriceGtz     == tmpVoucherLines.PurchPriceGtz
                && sizeLine.PurchPriceVend       == tmpVoucherLines.PurchPriceVend
                && sizeLine.LinePercentGtz    == tmpVoucherLines.LinePercentGtz
                && sizeLine.LinePercentVend      == tmpVoucherLines.LinePercentVend;

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 236,394 Most Valuable Professional on at
    RE: ORDER By in x++ sql statement

    This code returns just a single record - the one with the lowest InventSizeId.

    I think you're confused by the fact that strings aren't sorted in the same way as numbers. Number 10 is higher than number 7.5, but a string starting with "1" is lower than a string starting with "7".

  • CRSW Profile Picture
    CRSW 625 on at
    RE: ORDER By in x++ sql statement

    Right, but is there a way to get the records sorted as in inventsizeid is a number not a string

    What I Need is the lowest and the highest InventSizeid. So I runthis Code twice. first with orderby asc and second with desc.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 236,394 Most Valuable Professional on at
    RE: ORDER By in x++ sql statement

    Well, is it guaranteed that all InventSizeIds for all items can be converted to numbers? If not, you seem to be doing a wrong thing.

    For instance, you might have sizes like XXL.

    If all really can be converted to numbers, then you can create a view and add a computed column, which will contain InventSizeId coverted to a float number. Then you can sort by this field.

    If you have a mix of values, you may want to adjust numeric strings to be sortable in the way you want (e.g. 07 instead of 7).

  • CRSW Profile Picture
    CRSW 625 on at
    RE: ORDER By in x++ sql statement

    Yes in our case it is guaranteed

  • Blue Wang Profile Picture
    Blue Wang on at
    RE: ORDER By in x++ sql statement

    Hi CRSW,

    Sorting string fields is usually done，the first thing you need to make sure is that the format, length, etc. are the same. for example set 1 to 01.

    You can add new fields to the table and keep the numeric columns sorted.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 2,004

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 857 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 548 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans