I have a stored procedure that has two SQL statements connected by a UNION ALL however I am needing to add an ORDER BY to each SQL statement in order to give the user an option to sort the data based on a @sort_by parameter passed from an ASP page when a column name is clicked. Any HELP would be appreciated. Thank you!!

Here is the SQL statement (it is pretty long, sorry):

DECLARE @date datetime
DECLARE @date2 datetime
DECLARE @date3 datetime
SET @date=convert(datetime,convert(varchar(10),getdate (),101))
--current date with no time so get's 12:00AM
SET @date2=datepart(m,@date)
SET @date3=datepart(yyyy,@date)

BEGIN
SELECT DISTINCT hu.hcin_user_id as 'Physician_hcin_user_id', MIL.Hcin_user_id as 'Surrogate_hcin_user_id',
        rtrim(hu2.lastname) + ', ' + rtrim(hu2.firstname) as 'Surrogate_User',
        wl.Access_Date, wl.Access_Page, wl.Access_Opertn, wl.Report_Name, wl.Patient_Last, wl.Patient_First, wl.Patient_ID,
        wl.Med_Rec_No, hlo.ods_enterprsfacilitycode, crr.Descrptn as 'Relationship_Access'
    FROM hcin_user hu
    LEFT OUTER join hosp_status_link HSL
    ON HSL.Hcin_user_ID = hu.Hcin_user_Id -- get hosp affiliations of drs.
    LEFT OUTER join Hcin_Module_Info HMI
    ON HMI.Hcin_user_id = hu.hcin_user_id -- get physician hcin_user_id of persons mf id
    AND HMI.Description = 'PHIS_ID'
    LEFT OUTER Join mainframe_ID_lookup MIL
    ON hmi.value = MIL.Mainframe_id --get hcin_user_Ids of surrogates
    LEFT OUTER join hcin_user hu2
    ON mil.hcin_user_id = hu2.hcin_user_id --get names of surrogates
    LEFT OUTER join web_log wl
    ON wl.hcin_user_id = mil.hcin_user_id --get weblog activity
    LEFT OUTER join hospital_link_ods hlo
    ON wl.hospital_id = hlo.hospital_id
    LEFT OUTER JOIN clinical_reporting_relationships crr
    ON wl.relationship_id = crr.relatnshp_id --get relationship information
    WHERE hu.isphysician = 1
    AND substring(MIL.Mainframe_ID,4,3) <> 'ALL' -- ignore "All" mainframe ID's
    AND HSL.status_ID not in (1,15) -- status is not denied
    AND hu.helpdesk_clientid <> 10900 -- eliminate pss staff
    AND hu.accountStatus not in (-1, -3, -6) -- eliminate users disabled by pss, accnt expired or disabled by phys from caregate
    AND datepart(month, wl.access_date) = @date2
    AND datepart(year, wl.access_date) = @date3
    AND hu.hcin_user_id = 469
    AND crr.Descrptn IS NOT NULL
    AND wl.report_name <> 'patient selection'
    GROUP BY mil.hcin_user_id, hu.hcin_user_id, hu2.lastname, hu2.firstname, wl.access_date, wl.access_page, wl.access_opertn,
        wl.report_name, wl.patient_last, wl.patient_first, wl.patient_id, wl.med_rec_no, hlo.ods_enterprsfacilitycode, crr.descrptn

    ORDER BY wl.access_date


    UNION ALL

    --Presby Physicians and their surrogates.

    SELECT DISTINCT crm.hcin_user_id as 'Physician_hcin_user_id',
        crm.surrogate_id as 'Surrogate_hcin_user_id', rtrim(hu.lastname) + ', ' + rtrim(hu.firstname) as 'Surrogate_User',
        wl.Access_Date, wl.Access_Page, wl.Access_Opertn, wl.Report_Name, wl.Patient_Last, wl.Patient_First, wl.Patient_ID,
        wl.Med_Rec_No, hlo.ods_enterprsfacilitycode, crr.Descrptn as 'Relationship_Access'
    FROM clinical_reporting_main crm
    INNER Join Hcin_user hu
    ON hu.hciN_user_id = crm.surrogate_id
    INNER join web_log wl
    ON wl.hcin_user_id = crm.surrogate_id
    LEFT OUTER join hospital_link_ods hlo
    ON wl.hospital_id = hlo.hospital_id
    LEFT OUTER JOIN clinical_reporting_relationships crr
    ON wl.relationship_id = crr.relatnshp_id
    WHERE accountstatus = 0 -- active accounts only

    AND datepart(month,wl.access_date) = @date2
    AND datepart(year, wl.access_date) = @date3
    AND crm.hcin_user_id = 469
    AND crr.Descrptn IS NOT NULL
    GROUP BY crm.surrogate_id, crm.hcin_user_id, wl.patient_id, hu.lastname, hu.firstname, wl.access_date,
        wl.access_page, wl.access_opertn, wl.report_name, wl.patient_last, wl.patient_first, wl.patient_id,
        wl.med_rec_no, hlo.ods_enterprsfacilitycode, crr.descrptn
    ORDER BY wl.access_date

END

Sorry, but I didn't say that I am getting the following error when trying to submit this SP:

Server: Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'UNION'.

Again, any help would be appreciated. Thank you.

The ORDER BY clause is the last clause processed in a SELECT statement. There can only be one ORDER BY clause in a SELECT. The rows sorted are those rows after the UNION (and everything else) is performed.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff, thanks for your time and input. That got it.

You can not order the results of the individual result sets of the SQL statements in a UNION statement, you can however order the entire result set. This makes sense really, because why would you want to order results and then add more results to it? Surly you want to order all of the results once they have been combined. All you need to do is remove the first ORDER BY wl.access_date and leave the last one.

Regards
Owain Williams

Sorry for the duplicate answer, Jeff got there while I was typing my reply :D

Regards
Owain Williams

In the computer business, this is known as a 'race condition'.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com