Order By being ignored in SQL Server 2008 with multiple subqueries

Question

I have a report that returns a list of visits made by a patient however the order of visits is completely scrambled. The list of visits is obtained within a subquery like so:

INNER JOIN (SELECT subject_id,
                               A.id [VisitID],
                               A.visit_schedule_id [VisitScheduleID],
                               B.client_visit_description [Visit],
                               visit_date_local [Visit Date],
                               call_date [Call Date]
                FROM tbl_visit A
                INNER JOIN tbl_visit_schedule B ON B.id = A.visit_schedule_id) F

There is an inner query that uses this "F" block to grab relevant values to display to the end-user like so:

SELECT TOP 9999999999
           A.[name] [Country Name], 
           B.[site_number] [Site Number], 
           D.[investigator_name] [Investigator Name], 
           C.[spid] [Patient Number], 
           E.[YOB] [Year of Birth], 
           F.[Visit] [Visit], 
           CONVERT(NVARCHAR, F.[Visit Date], 120) [Visit Date],
           ISNULL(pl.[Pack(s) Assigned], 'N/A') [Kit Assigned],
           H.[Confirmation (doc)]
    FROM   (

And finally there is an outer select which displays to the user:

SELECT DISTINCT
   [Country Name], 
   [Investigator Name], 
   [Patient Number], 
   [Year of Birth], 
   --[Visit], 
   [Visit Date],
   [Kit Assigned],
   [Confirmation (doc)]
FROM   (

I have an ORDER BY for the inner select statement that looks like this:

ORDER BY CAST(F.[Visit Date] AS DATE) asc, F.[Call Date] asc

The issue is that when I have everything displaying ('Visit' isn't commented out) SQL returns the results in an un-ordered manner, even with the order by at the end. If I comment out Visit however, the results begin to order.

I'm not sure what significance this one particular column has on the rest of the report that is causing order by to fail, does anyone else have any clues on the matter?

Update: here's the entire query:

SELECT DISTINCT site_id, site_number, 
       [Country Name], 
       [Investigator Name], 
       [Patient Number], 
       [Year of Birth], 
       [Visit], 
       [Visit Date],
       [Kit Assigned],
       [Confirmation (doc)]
FROM   (SELECT TOP 99999999999
        B.[id] [site_id], 
               B.[site_number] [site_number], 
               A.[name] [Country Name], 
               B.[site_number] [Site Number], 
               B.[site_number] [SiteNumber], 
               D.[investigator_name] [Investigator Name], 
               C.[spid] [Patient Number], 
               E.[YOB] [Year of Birth], 
               F.[Visit] [Visit], 
               CONVERT(NVARCHAR, F.[Visit Date], 120) [Visit Date],
               ISNULL(pl.[Pack(s) Assigned], 'N/A') [Kit Assigned],
               H.[Confirmation (doc)]
        FROM   (
                SELECT [id], 
                       [name], 
                       [code] [country_code] 
                FROM   [dbo].[tbl_country]) A 
               INNER JOIN [dbo].[tbl_site] B 
                       ON A.[id] = B.[country_id] 
               INNER JOIN [dbo].[tbl_subject] C 
                       ON B.[id] = C.[site_id] 
               LEFT JOIN (SELECT A.id, 
                                 A.inv_first_name + ' ' + A.inv_last_name 
                                 [investigator_name] 
                          FROM   vw_site A) D 
                      ON B.[id] = D.[id] 
               INNER JOIN (SELECT A.[subject_id], 
                                  B.int_value [YOB] 
                           FROM   vw_subject_info A 
                                  INNER JOIN tbl_custom_data B 
                                          ON B.row_id = A.subject_id 
                                             AND B.table_name = 'Subject' 
                                             AND B.[field_name] = 'Initial_DOB') 
                          E 
                       ON C.[id] = E.[subject_id] 
               INNER JOIN (
                            SELECT subject_id,
                                   VisitID,
                                   VisitScheduleID,
                                   site_id,
                                   site_number,
                                   visit [Visit],
                                   visit_date_local [Visit Date],
                                   call_date [Call Date]
                            FROM vw_visit
                           ) F 
                       ON C.[id] = F.[subject_id] 
              LEFT JOIN (SELECT [visit_id],
                          [int_value] [Confirmation (doc)]
                          FROM  [vw_visitDetail]
                          WHERE  [field_name] = 'docId'
                          ) H
                      ON F.[VisitID] = H.[visit_id]
              --Selecting the packs dispensed at latest dispensing visit              
               LEFT JOIN (SELECT A.[id] [visit_id],
               STUFF((SELECT ', ' + CAST(C.[client_pack_number] AS NVARCHAR(MAX))
                      FROM   [dbo].[tbl_visit_pack] B
                      INNER JOIN [dbo].[tbl_pack] C
                      ON B.[pack_id] = C.[id]
                      INNER JOIN dbo.tbl_pack_type pt
                      ON pt.id = c.pack_type_id
                      WHERE  A.[id] = B.[visit_id]    
                      FOR XML PATH('')), 1, 2, '') AS [Pack(s) Assigned]
               FROM   [dbo].[tbl_visit] A
               GROUP  BY A.[id]) pl
               ON pl.visit_id = F.visitid
               --Ordering
               ORDER BY CAST(F.[Visit Date] AS DATE) asc, F.[Call Date] asc
) rawquery 
--where

Order By being ignored in SQL Server 2008 with multiple subqueries

Answers (1)

Related Questions