Tsql Union Query is breaking sorting in my asp.net gridview

Question

Hello All,

I have a stored procedure that works fine on its own. A recent requirement has made me think a Union query will accomplish what I need. Here is the working version. It's uses the ROW_NUMBER() to accomplish paging and sorting correctly

SELECT x.TicketID,
 x.TicketNumber,
 x.AccountID,
 x.SkillID
FROM (
SELECT  ROW_NUMBER() OVER(ORDER BY
    CASE WHEN @ipv_SortExpression = 'TicketNumber' AND @ipv_SortDirection = 'ASC' THEN y.TicketNumber END ASC,
    CASE WHEN @ipv_SortExpression = 'TicketNumber' AND @ipv_SortDirection = 'DESC' THEN y.TicketNumber END DESC,             
    CASE WHEN @ipv_SortExpression = 'AccountNumber' AND @ipv_SortDirection = 'ASC' THEN y.AccountNumber END ASC,
    CASE WHEN @ipv_SortExpression = 'AccountNumber' AND @ipv_SortDirection = 'DESC' THEN y.AccountNumber END DESC,             
    CASE WHEN @ipv_SortExpression = 'OpenDate' AND @ipv_SortDirection = 'ASC' THEN y.OpenDate END ASC,
    CASE WHEN @ipv_SortExpression = 'OpenDate' AND @ipv_SortDirection = 'DESC' THEN y.OpenDate END DESC                             ) AS RowNumber,
y.TicketID,
y.TicketNumber,
y.AccountID,
 y.SkillID
FROM ( SELECT 
    t.TicketID,
    t.TicketNumber,
    t.AccountID,
    t.SkillID  FROM someTable t )  AS y

) AS x WHERE x.RowNumber BETWEEN @startIdx AND @endIdx 

Please note I've ommitted some fields to shorten the query. This works fine as expected and when the procedure is called the result set is returned sorted properly. Now I need to add one additional record to the result set. I tried a Union query, but for some reason it breaks the sorting and the result set is always returned in the same order...

SELECT x.TicketID,
 x.TicketNumber,
 x.AccountID,
 x.SkillID
FROM (
SELECT  ROW_NUMBER() OVER(ORDER BY
    CASE WHEN @ipv_SortExpression = 'TicketNumber' AND @ipv_SortDirection = 'ASC' THEN y.TicketNumber END ASC,
    CASE WHEN @ipv_SortExpression = 'TicketNumber' AND @ipv_SortDirection = 'DESC' THEN y.TicketNumber END DESC,             
    CASE WHEN @ipv_SortExpression = 'AccountNumber' AND @ipv_SortDirection = 'ASC' THEN y.AccountNumber END ASC,
    CASE WHEN @ipv_SortExpression = 'AccountNumber' AND @ipv_SortDirection = 'DESC' THEN y.AccountNumber END DESC,             
    CASE WHEN @ipv_SortExpression = 'OpenDate' AND @ipv_SortDirection = 'ASC' THEN y.OpenDate END ASC,
    CASE WHEN @ipv_SortExpression = 'OpenDate' AND @ipv_SortDirection = 'DESC' THEN y.OpenDate END DESC                             ) AS RowNumber,
y.TicketID,
y.TicketNumber,
y.AccountID,
 y.SkillID
FROM ( SELECT 
    t.TicketID,
    t.TicketNumber,
    t.AccountID,
    t.SkillID  FROM someTable t )  AS y

) AS x WHERE x.RowNumber BETWEEN @startIdx AND @endIdx 
    UNION 
SELECT 
    z.TicketID
    z.TicketNumber
    z.AccountID
    z.SkillID
FROM (

SELECT TOP 1 
    tix.biTicketID as TicketID
    , tix.vcTicketID as TicketNumber
    , tix.biAccountID as AccountID
    , tix.siSkillID as SkillID
FROM someOtherTable tix ORDER BY tix.ActionDate ) As z

This query breaks sorting on my grid. It's like the procedure completely disregards the sorting criteria I am passing in. I have tried placing the smaller query first, and then Unioning the larger query, still no luck. Can anyone out there see what I'm doing incorrectly or how I can remedy this? The client wants this new functionality, but they do no want to sacrifice sorting. How can I correctly accomplish my task? Thanks a bunch for any advice or tips.

Cheers, ~ck in San Diego

Answer 1

You need an ORDER BY on the whole result set if you want the results to be ordered.

(Query A)
UNION 
(Query B)
ORDER BY xxx