Top 20 query problem

[Fareng]

Top 20 query problem

I am having problems with a top 20 query pulling more that 20 records. Here is the code

SELECT TOP 20 [Total Failure Pareto - Step 1].strMaterial_Num, Sum([Total Failure Pareto - Step 1].dblQty) AS dblQty, [Exclude Material From Pareto Chart].Exclude_Material_No
FROM [Exclude Material From Pareto Chart] RIGHT JOIN [Total Failure Pareto - Step 1] ON [Exclude Material From Pareto Chart].Exclude_Material_No = [Total Failure Pareto - Step 1].strMaterial_Num
GROUP BY [Total Failure Pareto - Step 1].strMaterial_Num, [Exclude Material From Pareto Chart].Exclude_Material_No HAVING ((([Exclude Material From Pareto Chart].Exclude_Material_No) Is Null))
ORDER BY Sum([Total Failure Pareto - Step 1].dblQty) DESC;

 

[SoCalAccessPro]

It's probably returning the top 20 records in the [Total Failure Pareto - Step 1] table, but returning more because of the join to the [Exclude Material From Pareto Chart] table.


Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[lameid]

It looks good to me. Is it consistent in what it does and what exactly is it doing?

 

[Fareng]

It is grabbing all 47 records and not limiting the list to the top 20

 

[lespaul]

and you know that if there are duplicates that both records will be returned, so you could get more than 20 records returned but some of them are duplicates.


Leslie

Have you met Hardy Heron?

 

[lameid]

It strikes me as weird that it does not work.

Try a left join for grins...

SELECT TOP 20 [Total Failure Pareto - Step 1].strMaterial_Num, Sum([Total Failure Pareto - Step 1].dblQty) AS dblQty, [Exclude Material From Pareto Chart].Exclude_Material_No 
FROM  [Total Failure Pareto - Step 1] LEFT JOIN [Exclude Material From Pareto Chart] 
	ON [Exclude Material From Pareto Chart].Exclude_Material_No = [Total Failure Pareto - Step 1].strMaterial_Num 

GROUP BY [Total Failure Pareto - Step 1].strMaterial_Num, [Exclude Material From Pareto Chart].Exclude_Material_No 
HAVING ((([Exclude Material From Pareto Chart].Exclude_Material_No) Is Null)) 
ORDER BY Sum([Total Failure Pareto - Step 1].dblQty) DESC;

If that doesn't work (and I doubt it would), try taking the top 20 out and saving the query. Make a query based on that and take the top 20 and see if you get the desired results.

Exploring SoCalAccessPro's theory... How many records does [Total Failure Pareto - Step 1] return? If less than 20, try making the top value less than the record count in that query to see if you end up with fewer results.

 

[Fareng]

I created another query to and did a top 20 on that and it appeared to work. I am using this data to crate a pareto chart so the order is critical. When I add the descending order to the query I get 47 records instead of the 20. I went back on tried this on the original code and the same thing happened.

 

[Fareng]

I was able to come up with a fix for the problem. When reviewing the Output I noticed that the qty that I was sorting on changed at record 19 and didn't change again until record 47. I added a hidden column with the sum of qty before that Material Number and did a sort on both of these fields and that worked well. Here is the code...

SELECT TOP 20 [Total Failure Pareto - Step 1].strMaterial_Num, Sum([Total Failure Pareto - Step 1].dblQty) AS dblQty, [Exclude Material From Pareto Chart].Exclude_Material_No
FROM [Exclude Material From Pareto Chart] RIGHT JOIN [Total Failure Pareto - Step 1] ON [Exclude Material From Pareto Chart].Exclude_Material_No = [Total Failure Pareto - Step 1].strMaterial_Num
GROUP BY [Total Failure Pareto - Step 1].strMaterial_Num, [Exclude Material From Pareto Chart].Exclude_Material_No HAVING ((([Exclude Material From Pareto Chart].Exclude_Material_No) Is Null))
ORDER BY Sum([Total Failure Pareto - Step 1].dblQty) DESC , [Total Failure Pareto - Step 1].strMaterial_Num DESC;

Thanks for the help

 

[lameid]

So the Top predicate gives you the top values whith any matching data based on repeats of the Order By clause. That is not what I expected...

Having read the online help topic, I also learned that distinctrow apparently effects tables in the select statment and not the from tables or netting of the join in the from clause. So if you join customers and orders and only customers is in the select and you use distinctrow, you only get 1 record per customer regardless of how many orders each has.