Yeo Meng Tat Yeo Meng Tat - 5 months ago 15
SQL Question

Display second query first - SQL Server

ALTER PROCEDURE [dbo].[SP]
AS
BEGIN
SET NOCOUNT ON;
SELECT CMS_ORG.GUID, CM_ORG.NAME AS Name
FROM CMS_ORG
UNION
SELECT CMS_ORG.GUID, CMS_ORG_HISTORY.NAME
FROM CMS_ORG_HISTORY INNER JOIN CMS_ORG ON CMS_ORG_HISTORY.GUID = CMS_ORG.GUID
UNION
SELECT CMS_SHARE.GUID,CMS_SHARE.NAME
FROM CMS_ORG INNER JOIN CMS_SHARE ON CMS_ORG.GUID = CMS_SHARE.GUID
END


Is it possible for me to display the second query as the first output instead of the third query. When I execute the stored procedure, the output of third query displays first then, first query and lastly second. How do I sort it to display accordingly given that the GUID are all equal?

CMS_SHARE.NAME => CMS_ORG.NAME => CMS_ORG_HISTORY.NAME
CMS_ORG_HISTORY.NAME => CMS_SHARE.NAME => CMS_ORG.NAME (I want this)

Answer

Add another parameter and order by it:

ALTER PROCEDURE [dbo].[SP]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CMS_ORG.GUID, CM_ORG.NAME AS Name, 1 as ord
    FROM CMS_ORG 
    UNION
    SELECT CMS_ORG.GUID, CMS_ORG_HISTORY.NAME, 2 as ord
    FROM CMS_ORG_HISTORY INNER JOIN CMS_ORG ON CMS_ORG_HISTORY.GUID = CMS_ORG.GUID 
    UNION
    SELECT CMS_SHARE.GUID,CMS_SHARE.NAME, 3 as ord
    FROM  CMS_ORG INNER JOIN CMS_SHARE ON CMS_ORG.GUID = CMS_SHARE.GUID 
    order by ord
END

Just change the numbers in the order you want.