ansaurus

Question

"SELECT TOP", "LEFT OUTER JOIN", "ORDER BY" gives extra rows

Answer 1

+1 A:

The behaviour you are seeing is not because the field is retrieved through a LEFT OUTER JOIN it is because the field is NULL

NULL in SQL does not behave like any other value

If a and b are both NULL then a = b is false. Thus when comparing for grouping access sees all the NULLs as different values

In this case if you want to use TOP you could exclude the NULL values by adding

WHERE tblRegion.Region IS NOT NULL

Mark 2010-03-09 09:47:00

I know about the syntactic difference between comparing `NULL`; that's not the problem here. I want the `NULL` results from the query, but just the first 25, so using your suggestion would give me incorrect results. The query in my question, when run on SQL Server, I am confident will work as I expect, so my question is why is Access treating it differently here.

Codesleuth 2010-03-09 09:55:40

what is the calculation that could be done to choose 25 out of 423? They are all equally different so how could access decide? What is the business rule that defines which 25 are the ones you want?

Mark 2010-03-09 10:15:27

The calculation is `TOP 25`, i.e. the first 25 results from the result set. This piece of code is part of a website which includes pagination, and hence requires using the `TOP` clause to limit the amount of data collected by the engine.

Codesleuth 2010-03-09 10:21:18

My question is what criteria is there to say a particular record is in the top 25 and not in the next 25. There has to be some exact ordering of the data and just using NULL does not give an ordering

Mark 2010-03-09 14:17:51

Usually when selecting data with no ordering, the result set returns in the order of the primary key, or in the order they were added to the table. I can see your point now, and I guess when it comes to outer joins Access can't work out a default order correctly. This is probably why adding another sort column fixes the problem. Thanks :)

Codesleuth 2010-03-09 15:15:57

The order is not guarnteed to be in PK or when added to table order although access is simple enough that that might be true. We had interesting results when upgrading one Sybase version (so probably MS Sql Server as well) when the order changed - so always explicitly provide an order by top() or if directly showing the data

Mark 2010-03-09 15:32:43

Answer 2

+1 A:

I don't have Access installed or your schema for that matter but does this work?

SELECT  TOP 25 
        tblClient.ClientCode, 
        tblRegion.Region
FROM    (tblClient LEFT OUTER JOIN
            tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY NZ(tblRegion.Region,'')

CResults 2010-03-09 10:01:39

I just tried it and got the following error: `Error Source: Microsoft JET Database Engine` `Error Message: Undefined function 'NZ' in expression.`

Codesleuth 2010-03-09 10:04:49

By the way, this is an Access 2002 Database. That could be important :s

Codesleuth 2010-03-09 10:05:55

I tried `ORDER BY IIF(ISNULL(tblRegion.Region), '', tblRegion.Region)` which I think is what you were going for, but this gave the same results. It's strange...

Codesleuth 2010-03-09 10:15:13

Answer 3

+2 A:

I've seen this before and then it was because Access only returned 25 rows if the 25th column used in the ORDER BY was unique. If it recurred Access also returned the tied values meaning that it can return more than 25 rows in one ORDER BY and exactly 25 in another.

So if the end of the ORDER BY hits NULL it would show all tied (NULL) values. This kind of bug is probably fixed in newer versions of Access, but asI don't have Access on this machine, but you could try:

select top 5 {1,2,3,4,5,5,5,5} ascending and descending to see if it applies to your version of Access.

HTH

amelvin 2010-03-09 10:14:52

Thanks for your answer - it got me thinking about how Access determines the uniqueness of the rows, and was what led me to try ordering by a second (non-NULL) field. +1, thanks again :)

Codesleuth 2010-03-09 10:46:56

@Codesleuth congrats on passing 1000 reputation BTW.

amelvin 2010-03-09 11:22:04

Answer 4

A:

When I also ORDER BY tblClient.Client the query appears to work:

SELECT  TOP 25
        tblClient.ClientCode,
        tblRegion.Region
FROM    (tblClient LEFT OUTER JOIN
            tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblRegion.Region, tblClient.Client

Since I don't really mind if I sort by a second field, for now I will do this.

I've updated by question to reflect this as a possible solution.

Codesleuth 2010-03-09 10:40:26

I've accepted this as the answer as this was my resolution, but if anyone can offer another solution, I will certainly consider that :)

Codesleuth 2010-03-12 15:24:57

ansaurus

tags:

views:

answers:

"SELECT TOP", "LEFT OUTER JOIN", "ORDER BY" gives extra rows

related questions