Access ignores Order By in SQL Server queries

B

bruce_at_lmc

New member

Local time

Today, 13:32

Joined

May 14, 2009

Messages

4

• May 14, 2009

• #1

Hi,

This makes no sense to me....

I have a SQL Server 9 (2008?) database in the cloud, and I've created an Access 2007 project (.adp). The connection works, I can see the tables and queries that I've created in SS, but when I double-click on a SS query in Access the data is correct but it isn't sorted like it should be.

When I run the same query in SS Mgmt Studio is sorts just fine.

What am I missing?

Thanks,
Bruce

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 10:32

Joined

Aug 30, 2003

Messages

36,267

• May 14, 2009

• #2

Welcome to the site. Does the query have an ORDER BY clause?

 

B

bruce_at_lmc

New member

Local time

Today, 13:32

Joined

May 14, 2009

Messages

4

• May 14, 2009

• #3

Yes .

 

D

dportas

Registered User.

Local time

Today, 18:32

Joined

Apr 18, 2009

Messages

76

• May 14, 2009

• #4

Is the "query" you refer to actually a SQL view? Views are always unordered. ORDER BY is only valid in a view as part of a TOP query in which case the purpose of ORDER BY is purely to specify the selection criteria used by TOP, not to sort the results.

If you want sorted results then use a stored procedure instead of a view.

 

B

bruce_at_lmc

New member

Local time

Today, 13:32

Joined

May 14, 2009

Messages

4

• May 14, 2009

• #5

Yes, I use the TOP predicate. The query is below.

SELECT TOP (100) PERCENT HairID, HairDescription, HairShort, DisplayOrder
FROM bruce.Hair
ORDER BY DisplayOrder

 

H

hanzz

New member

Local time

Today, 10:32

Joined

Oct 1, 2009

Messages

1

• Oct 1, 2009

• #6

maybe i have the answer

try this:

SELECT TOP(100) PERCENT WITH TIES HairID, HairDescription, HairShort, DisplayOrder
FROM bruce.Hair
ORDER BY DisplayOrder

that worked for me

 

B

bruce_at_lmc

New member

Local time

Today, 13:32

Joined

May 14, 2009

Messages

4

• Oct 2, 2009

• #7

Ah... Thanks for telling me this. In hind sight, this makes perfect sense.

Thanks for your time.

Bruce

dportas said:

Is the "query" you refer to actually a SQL view? Views are always unordered. ORDER BY is only valid in a view as part of a TOP query in which case the purpose of ORDER BY is purely to specify the selection criteria used by TOP, not to sort the results.

If you want sorted results then use a stored procedure instead of a view.

Click to expand...