CODEWITHSUNDEEP
sqlms-accessjet
jAC
jAC

Reputation: 5324

SQL statement returns wrong values (TOP n ... ORDER BY)

I tried to get the the last and next row of my table by using this query:

SELECT TOP 1 Nummer FROM Empfänger WHERE Nummer < 4748 ORDER BY Nummer DESC
UNION 
SELECT TOP 2 Nummer FROM Empfänger WHERE Nummer >= 4748 ORDER BY Nummer ASC;

The primary key “Nummer” is unique and auto-incremental , so there is 

4747, 4748, 4749

It should imho return the above stated numbers, but instead it returns:

4747, 4798, 4799

When I open the Database with Access and set the filter "between" the values are shown correctly.

I changed the statement to:

SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer < 4748 ORDER BY Nummer DESC
UNION 
SELECT DISTINCT Nummer FROM Empfänger WHERE Nummer = 4748 
UNION 
SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer > 4748 ORDER BY Nummer ASC;

This works nearly perfectly.

4747, 4748, 4750

When I write every statement in a single query it returns the right values... 

SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer < 4748 ORDER BY Nummer DESC
= 4747
SELECT DISTINCT Nummer FROM Empfänger WHERE Nummer = 4748 
= 4748
SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer > 4748 ORDER BY Nummer ASC;
= 4749

\

SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer < 4750 ORDER BY Nummer DESC
UNION 
SELECT DISTINCT Nummer FROM Empfänger WHERE Nummer = 4750 
UNION 
SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer > 4750 ORDER BY Nummer ASC;

I checked it again, suddenly the above shown query worked. Thank you for your help.

Upvotes: 1

Views: 1457

Answers (2)

Mike V
Mike V

Reputation: 444

Yes, Access is not ANSI-92 compliant in this area whereas SQL-Server is, and SQL-Server can be made to mimic the non-compliant behavior of Access as an option. I discovered this in a situation where I have the option of adding the id field, which is PK, to the ORDER BY and thus working around the problem. As long as your solution causes at least one field's value in the multi-field ORDER BY to be different in successive records, your work-around will obey the TOP parameter. If your key does not break on at least one field, then you can think of the SELECT TOP being enforced in this surprisingly amateurish way:

"Return the TOP n records and look at the value of the nth record's ORDER BY field(s) and include all the additional records that have that(those) value(s)".

Upvotes: 0

Colin &#39;t Hart
Colin &#39;t Hart

Reputation: 7729

Access uses the JET engine. It seems that ORDER BY is applied AFTER the TOP clause and you will need to use subqueries of the form

select top n ...
from (
  select ...
  from table
  where ...
  order by ...
)

to get it to work.

Upvotes: 4

Related Questions