CODEWITHSUNDEEP
sqlsql-servergroup-bysql-order-by
bhbennett3
bhbennett3

Reputation: 123

ORDER BY not working correctly on calculated column?

I need to create a table with the Top 1000 entities by a certain value. I am trying to create the table in one query. I cannot share my data, but my query looks like this:

SELECT TOP (1000) 
    [A], [B], [C],
    SUM([QTY]) AS Total_QTY
INTO 
    [db].[TopThousandEntities]
FROM 
    [db].[dt]
WHERE 
    [B] NOT LIKE '%Thing'
GROUP BY 
    [A], [B], [C]
ORDER BY 
    SUM([QTY]) DESC

What is happening is that my data is returned like this:

A    B    C  Calculated
------------------------
X    Y    Z    100
D    E    F     89

.... next 200-300 are correct i.e. descending in value... then

Q    R    T    92
E    I    U    88
O    U    Z    83

And the problem repeats in cycles until row 1000. Any thoughts on what could be going on?

Upvotes: 2

Views: 751

Answers (2)

devlin carnate
devlin carnate

Reputation: 8602

The grouping is applied before the ordering. To get the ordering your want, assuming you also want the TOP to be with regard to the Total_qty, move ORDER BY and TOP to an outer select clause, like so:

SELECT TOP (1000) c.*
FROM (
   SELECT 
        [A]
        ,[B]
        ,[C]
        ,SUM([QTY]) AS Total_QTY
    FROM [db].[dt]
    WHERE [B] NOT LIKE '%Thing'
    GROUP BY [A]
        ,[B]
        ,[C]
    ) x
ORDER BY x.Total_QTY DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Your issue is that you are checking the data using:

select *
from [db].[TopThousandEntities];

SQL tables represent unordered sets. There is no ordering unless you specify an ORDER BY clause.

So, if you want to see them in order:

select *
from [db].[TopThousandEntities]
order by Total_QTY desc;

If you want to preserve the original ordering in the table, you can add an identity column:

SELECT TOP (1000) IDENTITY(int) as TopThousandEntities_id,
       [A], [B], [C],
       SUM([QTY]) AS Total_QTY
INTO  [db].[TopThousandEntities]
FROM [db].[dt]
WHERE [B] NOT LIKE '%Thing'
GROUP BY [A], [B], [C]
ORDER BY SUM([QTY]) DESC;

You can now order by the identity column instead:

select *
from [db].[TopThousandEntities]
order by TopThousandEntities;

Upvotes: 2

Related Questions