View loses Order By when linked to Excel 1

[Cpreston]

Hi

I have a view with some order by fields. When ran in SQL the result is perfect.

When I link into Excel 2016 it loses the sort order. I have goggled it and seems a common issue but more so from Access, which of course I am not suing.

Does anyone know a solution for this at all please. Sorry if it is posted in the wrong Posting area, if it is please point me in the right direction.

Thanks

 

[Andrzejek]

Why do you have your view already ordered in the DB? Why not order it when you use it?


---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

I assume your views are SQL Server, so the reason could be in SQL Server, but how are the views defined, do the views have an ORDER BY clause?

If not, no order is guaranteed. Also If you use queries in Excel to [tt]SELECT something FROM SQLServerView[/tt] this alone means order is lost.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Cpreston]

Hi

It is in SQL View and I connect to it via Excel using Data/Other data sources\SQl etc.....

It is ordered in my view, I will attach the code from the view. If I run the view in SQl I get the perfect result. As sson as I link to excel the order is not correct. The reason for a lot of order by clauses is to match what is being used on our business system. Thanks

SELECT      dbo.WorksOrderSchedule.ScheduleNumber, dbo.WorksOrderSchedule.ScheduleDate, dbo.MachineGroup.Name AS Machine, 
                      dbo.WorksOrderHeader.WorksOrderNumber, dbo.WorksOrderHeader.WorksOrderStatus, dbo.WorksOrderHeader.TotalVolumeOut, dbo.Product.ProductCode, 
                      dbo.WorksOrderLine.TotalVolume
FROM         dbo.Product INNER JOIN
                      dbo.WorksOrderLine ON dbo.Product.ProductID = dbo.WorksOrderLine.ProductID FULL OUTER JOIN
                      dbo.MachineGroup INNER JOIN
                      dbo.WorksOrderSchedule ON dbo.MachineGroup.MachineGroupID = dbo.WorksOrderSchedule.MachineGroupID RIGHT OUTER JOIN
                      dbo.WorksOrderScheduleLine AS WorksOrderScheduleLine_1 ON 
                      dbo.WorksOrderSchedule.WorksOrderScheduleID = WorksOrderScheduleLine_1.WorksOrderScheduleID FULL OUTER JOIN
                      dbo.WorksOrderHeader ON WorksOrderScheduleLine_1.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID ON 
                      dbo.WorksOrderLine.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID
WHERE     (NOT (dbo.WorksOrderHeader.WorksOrderStatus IS NULL)) AND (dbo.WorksOrderHeader.WorksOrderStatus <> 5) AND (NOT (dbo.Product.ProductCode LIKE 'mdf%')) 
                      AND (dbo.MachineGroup.MachineGroupID IN ('82', '83', 84))
ORDER BY dbo.WorksOrderSchedule.ScheduleNumber, WorksOrderScheduleLine_1.LineNumber, dbo.WorksOrderLine.LineType, dbo.WorksOrderLine.LineNumber, 
                      dbo.WorksOrderLine.WorksOrderLineID

 

[lameid]

Working under the principle that somethings are easier to fix than explain, I would recommend adding an Order By in Excel. I know MsQuery exists in Excel so this should be easy to do, assuming you are familiar with the feature.

That said I wonder if you are using a "Native Client" connection to SQL? If not, try that.

I am not an Excel expert... Based on what you are saying about your view, I would expect SQL to return the data sorted correctly, therefore I blame Excel or the connection.

Access I know likes to do a number of weird things with queries especially if it is being used by a report. I suspect something similar may be going on here but again not an Excel Expert.

As you suggest this may not be the best forum. I recommend (forum68) for Excel issues. Since the Issue is happening in Excel, I would start there. If you want to export, import or otherwise work with Excel in another application or code environment, I would start there.

 

[Andrzejek]

I can see from your code that you can not order your view (at this time) in Excel because you order your view by fields that are not part of the fields displayed in the view.
So easy way to fix that would be to include the fields you need to order your data by, and do it in Excel.
Unless lameid's suggestion with other connection would do the trick


---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

I second lameid,

but also I would perhaps decide for myself to never define an ORDER BY in a view definition, as a view is a point of view on some data in tables and tables are not ordered but seen as sets, the technical ability to sort views is something you could also avoid categorically and define order always to be specified when data is queried.

If you want a specifically ordered resultset, I would decide that should be done by a stored procedure, which either uses the view or is defined instead of the view and has that query as its code.

That way you make a clearer distinction than what technical limits allow you. There might be a better driver or way to apply the order twice in MsQuery, but as you now know that flaw you better avoid it instead of trying to find the specific conditions making it work. It feels like an unstable solution if the correct order depends on a specific driver and it adds to what needs to be documented as requirements you might not be able to fulfill forever.

Thus I prefer self-imposed rules I can keep in overview and always fulfill, like doing without ordered views.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Cpreston]

Thanks for the great replies, will try them out Monday and update you all.

Thanks

 

[Cpreston]

Hi

Update to let you know I have it working

1. I added all the fields needed in the sort into the view
2. I took the order by our of the view
3. Connected into Native SQL through Excel
4. Sorted in Excel

Now when I filter on the schedule number it orders the works order numbers correctly in line with our business system.

Thanks for all the advice