Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 TOP & COUNT pull more results than predicate val?

Author  Topic 

filf
Yak Posting Veteran

67 Posts

Posted - 2002-06-25 : 11:56:27
PLease explain this, I am sure the reason I am getting these results is because of a fundamentla concept of sql.

I have a query:
SELECT top 4 [billing name] , count([_customerID])as orderQty FROM OrderManagerTable group by [billing name] order by count([_customerID]) desc

The results of which returns 7 rows:
billing name orderQty
Ramjam Funkyboogaloo-Smythe 5
Mr Iama Test 4
Mr G. Waldmeyer 3
Web Worx K Williams 2
Mr D Stevens 2
3B Design 2
D T COATES 2

Now the reason for this I can plainly see [four customers have made 2 orders and Jet SQL cannot distinguish between them so all are returned in result set], but why does the jet sql engine not return just the first 5 results as specified?

How would through SQL get around this sort of issue?

Cheers. filf

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-25 : 12:06:33
quote:
why does the jet sql engine not return just the first 5 results as specified?
Ummmm, you specified TOP 4, not TOP 5.

Access always interprets TOP to mean the top distinct values, not the number of rows. There are 4 distinct values (5,4,3,2) so it will return all of the rows that match them. I don't know of a workaround, but if you do a forum search for "TOP" in the MS Access forum you'll find some more discussion on it.

Go to Top of Page

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-06-25 : 12:07:19
SELECT top 4 [billing name] , count([_customerID])as orderQty FROM OrderManagerTable group by [billing name] order by count([_customerID]) desc , [billing name]



Go to Top of Page

filf
Yak Posting Veteran

67 Posts

Posted - 2002-06-25 : 12:15:17
gottit, simple, kNew I was not far from the answer, and my first result is still logically correct. The more i think about this, the first result is more correct than what larsg has suggested if I look at this from the point of view of someone who wants to use this data in the real world. What larsg has provided is the specific answer from a developers point of view which is what I personally wanted.

Nice to know the specifics of this, thanx for the quick answers peeps, its good to crUiSE these excellent forums again, keepupthagudwork - filf.

Go to Top of Page
   

- Advertisement -