none
Queries and sort RRS feed

  • Question

  • Greetings,

    I have written a query and on the general tab where I specify the output columns, I set some columns to sort.

    When I run the query no sort occurs. When I look at the 'show query language' I see no order by clause. When I manually try to put in an order by clause it says I have a syntax error.

    Does this sort thing work? If so, how?

    Thanks

    David Z

    CMCB 1902

    Thursday, September 19, 2019 4:44 AM

All replies

  • Hello David Z,
     
    What's your WQL query?
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 19, 2019 4:53 AM
  • I didnt write the query in native WQL. I just used the gui to add the fields and specify the sort order.

    This is what comes out.

    select SMS_R_System.Name, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_G_System_PC_BIOS.SMBIOSBIOSVersion from  SMS_R_System inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_PC_BIOS.SMBIOSBIOSVersion is not null  order by SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_G_System_PC_BIOS.SMBIOSBIOSVersion

    Thursday, September 19, 2019 5:03 AM
  • Hello,
     
    "order by SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_G_System_PC_BIOS.SMBIOSBIOSVersion"
     
    It is sorted already. Is it what you expect?
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 19, 2019 5:15 AM
  • oops didnt see the order by clause already in there...

    when I run the query, the data is not sorted as expected.

    Thursday, September 19, 2019 5:22 AM
  • Hello,
     
    It would be better if you could provide partial results and what you expect.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 19, 2019 5:40 AM
  • Hello,
     
    Side Note. We also could sort the result in the results window by clicking the column name (ctrl + click when sorted by multiple column).
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 19, 2019 5:56 AM
  • Or... you could stop trying to use Queries like reports. To me, the Query node should be very, very seldom used. Use Sql Server Management Studio, or make reports. like..

    Select
      s1.NetBIOS_name0 as 'ComputerName',
      csys.Manufacturer0 as 'Manufacturer',
      csys.Model0 as 'Model',
      b.SMBIOSBIOSVersion0 as 'BiosVersion'
    from
      v_r_system s1
      join v_gs_pc_bios b on b.resourceid=s1.resourceid
      join v_gs_computer_system csys on csys.resourceid=s1.resourceid
    where
      b.SMBIOSBIOSVersion0 is not null
    order by
     csys.Manufacturer0,
     csys.Model0,
     b.SMBIOSBIOSVersion0


    Standardize. Simplify. Automate.

    Thursday, September 19, 2019 8:20 PM
  • Thanks Sherry. I know what you mean. I was using this just ot get some quick info - it was only a one off.
    Thursday, September 19, 2019 9:40 PM
  • I am aware of the manual sort - my question still is - why doesnt the query return the results in the sorted order as specified in the order by clause? My query comes back sorted by name.
    Thursday, September 19, 2019 9:41 PM
  • <warning, warning, snark mode on, apologies in advance>A sort-of in-joke we've heard bandied about in response to any "why" questions to which there is no obvious answer is along the lines of "because that's how the coders coded it".

    If you really, really want the behavior to change, and/or want to report it as a bug (because you can reliably repeat the issue), I suggest submitting to uservoice, and highlight what the behavior you want to be, and the exact steps to reliably and consistently reproduce the 'wrong behavior'.  If it's a relatively easy thing to fix, quite often your requested change will be in the next Tech Preview at least, and possibly included in the next major general release.  (of course, if it's a horrible painful code change, or can't be reliably reproduced... your uservoice suggestion may languish for a while, or even cancelled if not reproducible).

    https://configurationmanager.uservoice.com/forums/300492-ideas


    Standardize. Simplify. Automate.

    Sunday, September 22, 2019 8:00 PM