“SELECT TOP”, “LEFT OUTER JOIN”, “ORDER BY” gives extra rows?

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.

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.

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 Mar 9 '10 at 10:46 @Codesleuth congrats on passing 1000 reputation BTW. – amelvin Mar 9 '10 at 11:22.

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 be are both NULL then a = be 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.

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 Mar 9 '10 at 9:55 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 Mar 9 '10 at 10:15 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 Mar 9 '10 at 10:21 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 Mar 9 '10 at 14:17 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 Mar 9 '10 at 15:15.

SELECT TOP 25 tblClient. ClientCode, tblRegion. Region FROM (tblClient LEFT OUTER JOIN tblRegion ON tblClient.

RegionCode = tblRegion. RegionCode) ORDER BY NZ(tblRegion. Region,'').

By the way, this is an Access 2002 Database. That could be important :s – Codesleuth Mar 9 '10 at 10:05 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 Mar 9 '10 at 10:15.

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.

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 Mar 12 '10 at 15:24.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.