Top 10 SQL Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've created a query that displays the Top 10 customers by Bill to Customer Number. Here's the SQL:

SELECT TOP 10 [Profit Center], BilltoCustNbr
FROM qselBaseData
WHERE ((YearMth)=[Forms]![frmStartEndMth]![txtEndYearMth])
GROUP BY [Profit Center], BilltoCustNbr
ORDER BY Sum([Inv Sales]) DESC;

Problem is, I really need the Top 10 customers by Bill to Customer Number within each Profit Center. I've got 5 profit centers, so I would expect the output to contain (5 x 10) 50 records. Can someone provide some help?
 
Hi,



You have a primary key field, pk?


SELECT [Profit Center], BillToCustNbr, SUM([Inv Sale])
FROM qselBaseData As a
WHERE pk IN( SELECT TOP 10 b.pk
FROM qselBaseData AS b
WHERE b.[Profit Center]=a.[Profit Center]
AND b.YearMonth=Forms!fmrStartENdMth!txtEndYearMonth
GROUP BY b.BillToCustNbr
ORDER BY SUM(b.[Inv Sale]) DESC
)
GROUP BY [Profit Center], BillToCustNbr
ORDER BY [Profit Center], SUM([Inv Sale]) DESC



The inner most query find the top 10 records for a given [Profit Center],
referred by the outermost query, but pick only the "pk" value of these top
10 records. So, if the outermost pk value is in those retrieved pk values,
it belong to those we have to keep..





Hoping it may help
Vanderghast, Access MVP



Kirk P. said:
I've created a query that displays the Top 10 customers by Bill to
Click to expand...
Customer Number. Here's the SQL:
SELECT TOP 10 [Profit Center], BilltoCustNbr
FROM qselBaseData
WHERE ((YearMth)=[Forms]![frmStartEndMth]![txtEndYearMth])
GROUP BY [Profit Center], BilltoCustNbr
ORDER BY Sum([Inv Sales]) DESC;

Problem is, I really need the Top 10 customers by Bill to Customer Number
Click to expand...
within each Profit Center. I've got 5 profit centers, so I would expect the
output to contain (5 x 10) 50 records. Can someone provide some help?
 
Kirk,

If you have a multi-key PK, Dirks method will not work, and the alternative
I have to offer is rather brute force, but will work if implemented
properly.

First, create a query (qry1) that gives you the total sales by ProfitCenter
and BilltoCustNbr

SELECT [ProfitCenter], BilltoCustNbr, SUM([InvSales]) as MthSales
FROM qselBaseData
WHERE YearMth = Forms!frmStartEndMth!txtEndYearMth

Next, create a second query. This involves two copies of the previous
query, and uses a non-equi (not sure if I got that term correct) join. If
you don't do the grouping, what this query will get you is as many records
on the right side of the join as are less than or equal to the mthSales
figure on the left side. When you do the grouping and count the number of
records on the right side of the join for each record, what you essentially
get is the ranking of the individual, with the largest monthly sales figure
getting a ranking of 1, and so on. It has been a while since I had to do
this, but I think this air-code will work for multi-field PKs.

SELECT q1.ProfitCenter, q1.BilltoCustNbr, q1.mthSales
FROM qry1 q1
LEFT JOIN qry1 q2
ON q1.ProfitCenter = q2.ProfitCenter
AND q1.BilltoCustNbr = q2.BillToCustNbr
AND q1.mthSales >= q2.mthSales
ORDER BY q1.ProfitCenter, q1.mthSales DESC
GROUP BY q1.ProfitCenter, q1.BilltoCustNbr, q1.mthSales
HAVING Count(q2.ProfitCenter) <= 10

Just like using Top 10, this query will return more than 10 records per
ProfitCenter if the mthSales figures are the same for the 11th or higher
positions.

HTH
Dale

Kirk P. said:
I've created a query that displays the Top 10 customers by Bill to
Click to expand...
Customer Number. Here's the SQL:
SELECT TOP 10 [Profit Center], BilltoCustNbr
FROM qselBaseData
WHERE ((YearMth)=[Forms]![frmStartEndMth]![txtEndYearMth])
GROUP BY [Profit Center], BilltoCustNbr
ORDER BY Sum([Inv Sales]) DESC;

Problem is, I really need the Top 10 customers by Bill to Customer Number
Click to expand...
within each Profit Center. I've got 5 profit centers, so I would expect the
output to contain (5 x 10) 50 records. Can someone provide some help?
 
Back
Top