Order by clause in openrowset

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Order by clause in openrowset

Order by clause in openrowset

Rate Topic

Display Mode

Topic Options

Author

Message

vsjayashri

Posted Thursday, July 03, 2008 8:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 23, 2010 2:22 AM
Points: 44, Visits: 103

hi

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test\Test.xls;',
'SELECT id,col1 FROM [Sheet1$]')
SELECT id,col1 FROM temp_test order by id

Everything works fine....
but the order by clause is not recoginsed
when i use the query in analyser it works correctly with order by clause,
but while xporting it is not working.

Please help

Post #528101

Matija Lah

Posted Friday, July 25, 2008 10:33 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:44 PM
Points: 2,869, Visits: 370

A table is an unordered set. Order is guaranteed only when the ORDER BY clause is used in the outer query and only in retrieval operations. If ORDER BY is not used the Optimizer is free to insert the rows into the table regardless of the way they have been ordered at the source.

If you want to preserve a specific row order, you need to consider that at design time. You could, for instance, use an additional column to store the values you want to order the rows by - SQL Server cannot do that for you.

When inserting data into an Excel Worksheet the easiest thing to do is to open the Workbook and sort the rows using Excel's sorting methods. YOu can even do that programmatically. Either way you won't be able to completely prevent Excel users from changing the order of the rows if they choose to do so.

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Post #541123

ProgramAndPM

Posted Friday, December 07, 2012 12:50 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 07, 2012 12:49 PM
Points: 1, Visits: 3

SSCrazy is correct. I was able to get around this, at least temporarily by using "distinct" after the select. This might not always work.

Post #1394204

« Prev Topic | Next Topic »

Permissions