How to get the top 10 records for every salesperson

[snipe]

Hey guys,



I currently have a query chain that gets me every salesperson's sales, in descending order of dollar amount. I need to pull only the first (top) 10 for every salesperson. Whats the best way to do this?

[ItsMe]

Maybe in a query
SELECT TOP 10

[snipe]

But that only pulls the top 10 records. Not the top 10 for each salesperson. I can't make a separate query for every salesperson.

[June7]

Review http://allenbrowne.com/subquery-01.html#TopN

[snipe]

Ah nice June7. Looking into that now. Query is currently running (extremely slowly) so I may have to populate a temp table before I roll this out.

[snipe]

I can't seem to make this work...

SELECT T_TBS_2.CountOfSO_DOC_NUM, T_TBS_2.SO_STORE_CD, T_TBS_2.FirstOfSO_EMP_SLSP_CD1, T_TBS_2.SumOfLinePrice, T_TBS_2.CUST_CD, T_TBS_2.MaxOfSTAT_CD, T_TBS_2.SO_WR_DT, T_TBS_2.MaxOfFINAL_DT, T_TBS_2.ORD_TP_CDFROM T_TBS_2
WHERE T_TBS_2.CUST_CD IN
(SELECT TOP 10 CUST_CD
FROM T_TBS_2 AS Dupe
WHERE Dupe.CUST_CD = T_TBS_2.CUST_CD
ORDER BY Dupe.SumOfLinePrice DESC)
ORDER BY T_TBS_2.SO_STORE_CD, T_TBS_2.FirstOfSO_EMP_SLSP_CD1, T_TBS_2.SumOfLinePrice DESC;

I don't get an error, but I am still getting more than 10 from salespeople:


Any ideas?

[snipe]

Derp...

SELECT T_TBS_2.CountOfSO_DOC_NUM, T_TBS_2.SO_STORE_CD, T_TBS_2.FirstOfSO_EMP_SLSP_CD1, T_TBS_2.SumOfLinePrice, T_TBS_2.CUST_CD, T_TBS_2.MaxOfSTAT_CD, T_TBS_2.SO_WR_DT, T_TBS_2.MaxOfFINAL_DT, T_TBS_2.ORD_TP_CDFROM T_TBS_2
WHERE T_TBS_2.CUST_CD IN
(SELECT TOP 10 CUST_CD
FROM T_TBS_2 AS Dupe
WHERE Dupe.FirstOfSO_EMP_SLSP_CD1= T_TBS_2.FirstOfSO_EMP_SLSP_CD1
ORDER BY Dupe.SumOfLinePrice DESC)
ORDER BY T_TBS_2.SO_STORE_CD, T_TBS_2.FirstOfSO_EMP_SLSP_CD1, T_TBS_2.SumOfLinePrice DESC;

Subquery wasn't search by salesperson.... wow.

all good!