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?

                    New to ADSelfService Plus?

                    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", ...

                      • 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 ...

                      • Report on Organizational roles

                        DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 10000 select  r.orgrolename "orgrolename", r.description "Description", au.FIRST_NAME "Name", DepartmentDefinition.DEPTNAME "Department", SDOrganization.NAME "Site"  from aaauser au ...

                      • Change template count

                        SELECT    ct.NAME "Template",            Count(chdt.changeid) "count" FROM      changedetails chdt  LEFT JOIN changetemplate ct  ON        chdt.templateid=ct.templateid  WHERE     chdt.createdtime >= <from_lastmonth>  ...

                      • New request is created everytime when a user is sending mail to closed Request

                        Issue : New request is created everytime when a user is sending mail to closed or Resolved Request. Cause : In some cases, there will be no workorder id mentioned in the subject and that mail is not sent as reply from user for the notification or ...

                        Related Products