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.
| 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]) descThe results of which returns 7 rows:billing name orderQtyRamjam Funkyboogaloo-Smythe 5Mr Iama Test 4Mr G. Waldmeyer 3Web Worx K Williams 2Mr D Stevens 23B Design 2D T COATES 2Now 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. |
 |
|
|
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] |
|
|
|
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. |
|
|
|
|
|
|
|
|
TOP & COUNT pull more results than predicate val?