Top Ten (10) by ID from one table

[starbird2273]

OK, I am trying to get a query to run from one table. My results should be the top ten dollar balance amounts for EVERY Customer ID in a table.

Table Structure (Important Feilds, I can add supporting feilds later)

Customer ID
Open Balance Amount

I have tried the queries from the following links, as well as others, and only get the top ten dollars total (so ten records)

http://tek-tips.com/viewthread.cfm?qid=871162

http://tek-tips.com/viewthread.cfm?qid=836969

Also, not every customer may have ten records, so I would need to pull all the records (see customer 789 below)

Basically, what I want is the following for output.

CustomerID Open Balance Amount
123 $10000
123 $9000
123 $8000
123 $7000
123 $6000
123 $5000
123 $4000
123 $3000
123 $2000
123 $1000
456 $10000
456 $9750
456 $9500
456 $9250
456 $9000
456 $8750
456 $8500
456 $8250
456 $8000
456 $7750
789 $50
789 $30
789 $20

 

[PHV]

SELECT C.* FROM yourTable As C
WHERE C.[Open Balance Amount] In (SELECT TOP 10 [Open Balance Amount] FROM yourTable WHERE CustomerID = C.CustomerID ORDER BY 1 DESC)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[starbird2273]

Thanks for your help. This is what I have. Still just bringing up the top ten dollars.

This is my SQL - any thoughts? And also, if a MinOfPROV_TIN has less than 10 records, will it pull up all of them?

SELECT [Non Adjustments].MinOfPROV_TIN, [Non Adjustments].FIN_Recovery_Balance_Amount
FROM [Non Adjustments]
WHERE [Non Adjustments].FIN_Recovery_Balance_Amount in (
     select TOP 10 [Non Adjustments].FIN_Recovery_Balance_Amount       
from [Non Adjustments]
      where [Non Adjustments].MinOfPROV_TIN =  [Non Adjustments].MinOfPROV_TIN
order by [Non Adjustments].FIN_Recovery_Balance_Amount desc);

 

[DotNetGnat]

try this:

SELECT A.MinOfPROV_TIN, A.FIN_Recovery_Balance_Amount
FROM [Non Adjustments] A
WHERE A.FIN_Recovery_Balance_Amount in (
select TOP 10 FIN_Recovery_Balance_Amount
from [Non Adjustments] B
where B.MinOfPROV_TIN = A.MinOfPROV_TIN
order by B.FIN_Recovery_Balance_Amount desc);

and answer to your second question is YES

-DNG

 

[starbird2273]

Thank you. tried this and it just hangs. any performance ideas?

 

[PHV]

Have you indexed the MinOfPROV_TIN field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

Have you indexed the field" should be a forum FAQ

r937.com | rudy.ca

 

[starbird2273]

No I haven't, but will try that now.

Thanks.

~ S

 

[starbird2273]

Looks like that worked (indexing). Having the end user validate the results, but spot check looks good

thanks everyone

 

[starbird2273]

OK, looks good, except that I am getting some MinOfPROV_TIN that have all the records (over 100) when only the top 10 for them should be returned. any thoughts?

 

[PHV]

(over 100)
over 100 DIFFERENT amounts for SAME MinOfPROV_TIN ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[starbird2273]

Over 100 records. for the same MinOfPROV_TIN. I am looking for only the 10 highest FIN_Recovery_Balance_Amount

 

[PHV]

How many DIFFERENT FIN_Recovery_Balance_Amount values returned for that MinOfPROV_TIN ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[starbird2273]

AH....

Never more than 10 DIFFERENT amounts.

Is there a way to just get the top ten? and if there are more than one of an amount, I only get 10 total back?

My end result should return no more thant 10 records for each MonOfPROV_TIN. so if one MinOfPROV_TIN had $50 75 times and nothing else, I should get 10 back all at $50, for a total of $500. Likewise, if another MinOfPROV_TIN has one record of $100 and 90 records for $10, I would get back the one $100 record and then 9 $10 records...

Is this possible?

 

[PHV]

Any other fields in [Non Adjustments] ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[starbird2273]

Yes.

Here is the full code from the qry.

SELECT A.MinOfPRV_FULL_NM, A.MinOfPROV_PRTCP_STS_CD, A.SumOfTOTAL_PAID, A.MinOfPD_POL_NBR, A.MinOfSBSCR_NBR, A.MinOfREL_CD, A.MinOfFST_NM, A.MinOfLST_NM, A.MinOfMPIN, A.MinOfPROV_TIN_PRFX_CD, A.MinOfPROV_TIN, A.MinOfPROV_TIN_SUFX_CD, A.MinOfDERIV_MBR_MKT_NBR, A.CLM_AUD_NBR, A.MinOfDRFT_NBR, A.MinOfERLY_SRVC_DT, A.MaxOfLT_SRVC_DT, A.MinOfADJD_OFC_CD, A.MinOfENG_CD, A.SumOfTOTAL_CHARGED, A.MinOfCLM_PD_DT, A.MinOfOVRIDE_CD, A.MinOfTRANS_CD, A.MinOfTRANS_DESC, A.MinOfSRVC_CURR_IND, A.MinOfCLM_LOC_CD, A.DTL_Detail_Line_Number, A.FIN_Recovery_Balance_Amount, A.DTL_Audit_Reason_Code_Short_Description, A.DTL_Audit_Reason_Code_Long_Description, A.DTL_External_Notes
FROM [Non Adjustments] AS A
WHERE (((A.FIN_Recovery_Balance_Amount) In (select TOP 10 FIN_Recovery_Balance_Amount       
from [Non Adjustments] B
      where B.MinOfPROV_TIN =  A.MinOfPROV_TIN
order by B.FIN_Recovery_Balance_Amount desc)));

and for just the table

SELECT [Non Adjustments].MinOfPRV_FULL_NM, [Non Adjustments].MinOfPROV_PRTCP_STS_CD, [Non Adjustments].SumOfTOTAL_PAID, [Non Adjustments].MinOfPD_POL_NBR, [Non Adjustments].MinOfSBSCR_NBR, [Non Adjustments].MinOfREL_CD, [Non Adjustments].MinOfFST_NM, [Non Adjustments].MinOfLST_NM, [Non Adjustments].MinOfMPIN, [Non Adjustments].MinOfPROV_TIN_PRFX_CD, [Non Adjustments].MinOfPROV_TIN, [Non Adjustments].MinOfPROV_TIN_SUFX_CD, [Non Adjustments].MinOfDERIV_MBR_MKT_NBR, [Non Adjustments].CLM_AUD_NBR, [Non Adjustments].MinOfDRFT_NBR, [Non Adjustments].MinOfERLY_SRVC_DT, [Non Adjustments].MaxOfLT_SRVC_DT, [Non Adjustments].MinOfADJD_OFC_CD, [Non Adjustments].MinOfENG_CD, [Non Adjustments].SumOfTOTAL_CHARGED, [Non Adjustments].MinOfCLM_PD_DT, [Non Adjustments].MinOfOVRIDE_CD, [Non Adjustments].MinOfTRANS_CD, [Non Adjustments].MinOfTRANS_DESC, [Non Adjustments].MinOfSRVC_CURR_IND, [Non Adjustments].MinOfCLM_LOC_CD, [Non Adjustments].DTL_Detail_Line_Number, [Non Adjustments].FIN_Recovery_Balance_Amount, [Non Adjustments].DTL_Audit_Reason_Code_Short_Description, [Non Adjustments].DTL_Audit_Reason_Code_Long_Description, [Non Adjustments].DTL_External_Notes
FROM [Non Adjustments];

another unique feild is DTL_Detail_Line_Number

 

[PHV]

At the risk of performance degradation:

SELECT ...
FROM [Non Adjustments] AS A
WHERE FIN_Recovery_Balance_Amount & DTL_Detail_Line_Number In (
select TOP 10 FIN_Recovery_Balance_Amount & DTL_Detail_Line_Number from [Non Adjustments]
 where MinOfPROV_TIN = A.MinOfPROV_TIN order by FIN_Recovery_Balance_Amount desc);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[starbird2273]

Thank you. still coming up with the additional lines. but it ran quick

 

[PHV]

still coming up with the additional lines
Really ?
You said that : another unique feild is DTL_Detail_Line_Number

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[starbird2273]

it should be.

a quick pivot table proved this. any other thougts?

I really appreciate your help