Export from SQL Server to Excel with order by

Question

I am exporting from SQL Server to Excel from a saved view. In that view it is sorted by two columns (order by..)

But when I export it, it is randomly sorted in Excel.

How to export with the order by?

Here is the query of the view:

SELECT     TOP (100) PERCENT GETDATE() AS Date, MixStatusID AS Partner, SUM(CAST(Weight AS 
float)) AS Weight, SUM(CAST(TotalCostPrice AS float)) AS TotalCost, ProductGroupID
FROM         dbo.D_ParcelLocation
WHERE     (ParcelStatusID IN (N'stock', N'memo', N'transfer', N'analyze_un')) AND (ParcelTypeID <> N'M') AND (ProductGroupID <> N'G_POL') AND (ProductGroupID <> N'G_ROU')
GROUP BY MixStatusID, ProductGroupID
HAVING      (SUM(CAST(Weight AS float)) > 0)
ORDER BY ProductGroupID, Partner

Can you **show us** the view definition? (the SQL script used to create the view) — marc_s, May 17 '13 at 14:39
Please **do not** put code samples or sample data into comments - since you cannot format it, it's **extremely hard** to read it.... Instead: **update** your question by editing it to provide that additional information! Thank you. — marc_s, May 17 '13 at 14:58
`ORDER BY` does not work in a view (see [this question](http://stackoverflow.com/questions/1306359/order-by-in-a-sql-server-2008-view) and the [documentation](http://msdn.microsoft.com/en-us/library/ms188385(v=sql.105).aspx)). You must use `ORDER BY` in a query if you want sorted results. — Pondlife, May 17 '13 at 16:49
Sorry, I was away: SQL 2008 R2, I am trying add it to the export query, like this: CREATE TABLE `Z_STOCK_PARTNERS_2` ( `Date` DateTime, `ProductGroupID` LongText ) Order by `ProductGroupID` but it tells me the syntax is wrong — whatever61, May 28 '13 at 11:52

score 0 · Answer 1 · answered May 17 '13 at 18:49

Sorry, but can't you just create a sproc with the same resultset?

create procedure sp_D_ParcelLocation_s as
begin 
    SELECT     TOP (100) PERCENT GETDATE() AS Date
                , MixStatusID AS Partner
                , SUM(CAST(Weight AS float)) AS Weight
                , SUM(CAST(TotalCostPrice AS float)) AS TotalCost
                , ProductGroupID
    FROM        dbo.D_ParcelLocation
    WHERE       (ParcelStatusID IN (N'stock', N'memo', N'transfer', N'analyze_un')) 
    AND         (ParcelTypeID <> N'M') 
    AND         (ProductGroupID <> N'G_POL') 
    AND         (ProductGroupID <> N'G_ROU')
    GROUP BY    MixStatusID, ProductGroupID
    HAVING      (SUM(CAST(Weight AS float)) > 0)
    ORDER BY    ProductGroupID, Partner
end

Maybe that doesn't work? I don't work with Excel like this too often, but I think you can use a sproc just as well as a view.

I never worked with stored procedures.. After I will add it, what to do with it? — whatever61, May 28 '13 at 11:55

score 0 · Answer 2 · edited May 23 '17 at 11:43

As @Pondlife has correctly noted, if that query is in reality a view definition, you need to specify an ORDER BY when selecting from that view, i.e. something like this:

SELECT *
FROM YourView
ORDER BY ProductGroupID, Partner
;

or whatever the query looks like that you are using to actually transfer the data.

The ORDER BY in the view definition doesn't provide you with a fixed order of the result set. In fact, it is only allowed there because there's a TOP clause, and the formal¹ meaning of the ORDER BY in this case is to set the correct row order for the TOP clause, but not for the result of the query that would then be reading from this view.

^{¹For the actual meaning of using the SELECT TOP 100 PERCENT + ORDER BY technique, you can take a look at this thread: Why use Select Top 100 Percent?.}

Export from SQL Server to Excel with order by

2 Answers2