5.Order by

5.Order by



GROUP BY

If you want to group by 5th column then the order by should be in the same line.

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1') ORDER BY 5




If the ORDER BY is in the next line then it will sort by the 5th column.

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician"FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
ORDER BY 5



The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.      

SELECT wo.WORKORDERID "Request ID",
       std.STATUSNAME "Request Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE std.STATUSNAME = 'open'
ORDER BY 1 desc


UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.

Each SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement must also be in the same order

Example : The below query will combine active and archived request. 

SELECT wo.WORKORDERID "Request ID",
       std.STATUSNAME "Request Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE std.STATUSNAME = 'open'
UNION
SELECT arcwo.WORKORDERID AS "Request ID",
       arcwo.STATUSNAME AS "Request Status" FROM Arc_WorkOrder arcwo

COUNT

To get the count of Request we use group by option

SELECT ti.FIRST_NAME AS "Technician",count(wo.workorderid) "Count" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1') Group BY ti.FIRST_NAME




      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Purchase order approval details

                      SELECT po.POCUSTOMID AS "PO Number", max(po.PONAME) AS "PO Name", max(poa.FIRST_NAME) AS "Purchase Requester", longtodate(max(po.DATEORDERED)) AS "Ordered Date", longtodate(max(po.DATEREQUIRED)) AS "Required Date", max(pos.STATUSNAME) AS "PO Status", ...

                    • Query to show purchase order full details (PGSQL)

                      Tested in PGSQL build (14300) SELECT po.POCUSTOMID AS "Order No.",poi.serialno "S.No", poaddfield.UDF_CHAR1 AS "Legal Entity", po.PONAME AS "PO Name", pos.STATUSNAME AS "PO Status", cy.currencyname "Currency", paao.NAME AS "Vendor Name", po.SHIPTO AS ...

                    • Query Reports on Purchase Order Items (MSSQL)

                      Tested in build MSSQL (14306) MSSQL: 1. Login to SDP MSP as administrator 2. Execute this from SDP MSP application -> Reports -> New Query report Execute below query SELECT "po"."POCUSTOMID" AS "PO Number", "po"."PONAME" AS "PO Name", ...

                    • Query to show PO associated assets with Invoice details (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) SELECT po.Purchaseorderid "PO ID", MAX(po.poname) "PO Name", Max(ci.ciname) "CI Name", MAX(systeminfo.SERVICETAG) "Service Tag", MAX(systeminfo.MANUFACTURER) "Manufacturer", MAX(systeminfo.MODEL) ...

                    • Delhivery Integration (Plug-in mode)

                      Requirement:  Delhivery Integration 1. Create an order 2. Track order 3. Cancel order 4. Print packing slip Steps to be followed 1. Download the attached zip and exact it 2. Move order_resource.py, track_order.py, cancel_order.py, packingslip.py ...

                      Related Products