sorting in union all not successfull

Question

This SQL for using some purpose but even i give a sort clause(order by tdate) still it is not sorting date wise. how to rewrite to get 'tdate' column sorted:

SELECT TOP 100 PERCENT * 
FROM   (SELECT TOP 100 PERCENT tdate, 
                               parti, 
                               Abs(Sum(amtdr)) AS Dr, 
                               Sum(amtcr)      AS Cr, 
                               nart 
        FROM   dbo.dbo_dayb a 
        WHERE  ( account = 'bank' ) 
        GROUP  BY idno, 
                  tdate, 
                  parti, 
                  nart 
        UNION ALL 
        SELECT tdate, 
               parti, 
               amtdr, 
               amtcr, 
               nart 
        FROM   dbo.dbo_dayb 
        WHERE  ( account = N'PDC account' ) 
               AND ( post = N'Cr' )) DERIVEDTBL 
ORDER  BY tdate

Is tdate column of type DATETIME for sure? Maybe it is a varchar? — PacoDePaco, Sep 04 '16 at 08:59
@Pravi what is DERIVEDTBL ... could you please write the error on executing the query — dhS, Sep 04 '16 at 09:10
Possible duplicate of [Create a view with ORDER BY clause](http://stackoverflow.com/questions/15187676/create-a-view-with-order-by-clause) — Matthew Whited, Sep 04 '16 at 09:14

score 0 · Answer 1 · answered Sep 04 '16 at 09:06

 ((SELECT TOP 100 PERCENT * FROM 
 (SELECT TOP 100 PERCENT tdate, parti, 
 ABS(SUM(amtdr)) AS Dr, SUM(amtcr) AS Cr, nart FROM 
 dbo.dbo_dayb a 
 WHERE (account = 'bank') 
 GROUP BY idno, tdate, parti, nart) 

 UNION ALL 

 (SELECT tdate, parti, amtdr, amtcr, nart 
 FROM dbo.dbo_dayb 
 WHERE (account = N'PDC account') 
 AND (post = N'Cr')) DERIVEDTBL) )

 ORDER BY tdate

Try this

Matthew Whited · Accepted Answer · 2016-09-04T09:38:10.660

When returning data from views SQL may reorder you data even if you use an order by clause. One trick to get around this it to use ROW_NUMBER()

SELECT *, ROW_NUMBER() OVER (ORDER BY tdate) AS [ordinal]
FROM (SELECT tdate, parti, ABS(SUM(amtdr)) AS Dr, SUM(amtcr) AS Cr, nart
      FROM dbo.dbo_dayb a
      WHERE       (account = 'bank')
      GROUP BY idno, tdate, parti, nart
      UNION ALL
      SELECT      tdate, parti, amtdr, amtcr, nart
      FROM          dbo.dbo_dayb
      WHERE      (account = N'PDC account') AND (post = N'Cr')
) derived

As a note... the best way to get data out of a view in a particular order is to add an orderby to the query you are using to retrieve the data. (And that is the only way guaranteed to work)

sorting in union all not successfull

2 Answers2