[Solved] sorting in union all not successfull
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

sorting in union all not successfull

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

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `dbo_dayb` ADD INDEX `dbo_dayb_idx_accoun_idno_tdate_parti_nart` (`account`,`idno`,`tdate`,`parti`,`nart`);
ALTER TABLE `dbo_dayb` ADD INDEX `dbo_dayb_idx_account_post` (`account`,`post`);
The optimized query:
SELECT
        TOP 100 PERCENT * 
    FROM
        (SELECT
            TOP 100 PERCENT a.tdate,
            a.parti,
            Abs(Sum(a.amtdr)) AS Dr,
            Sum(a.amtcr) AS Cr,
            a.nart 
        FROM
            dbo.dbo_dayb a 
        WHERE
            (
                a.account = 'bank'
            ) 
        GROUP BY
            a.idno,
            a.tdate,
            a.parti,
            a.nart 
        UNION
        ALL SELECT
            dbo.dbo_dayb.tdate,
            dbo.dbo_dayb.parti,
            dbo.dbo_dayb.amtdr,
            dbo.dbo_dayb.amtcr,
            dbo.dbo_dayb.nart 
        FROM
            dbo.dbo_dayb 
        WHERE
            (
                dbo.dbo_dayb.account = N'PDC account'
            ) 
            AND (
                dbo.dbo_dayb.post = N'Cr'
            )
    ) DERIVEDTBL 
ORDER BY
    DERIVEDTBL.tdate

Related Articles



* original question posted on StackOverflow here.