Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Top Ten (10) by ID from one table

Status
Not open for further replies.

starbird2273

IS-IT--Management
Jul 27, 2005
26
US
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)



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
 
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
 
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?
Code:
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);
 
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
 
Thank you. tried this and it just hangs. any performance ideas?
 
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
 
Looks like that worked (indexing). Having the end user validate the results, but spot check looks good

thanks everyone
 
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?
 
(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
 
Over 100 records. for the same MinOfPROV_TIN. I am looking for only the 10 highest FIN_Recovery_Balance_Amount
 
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
 
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?
 
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
 
Yes.

Here is the full code from the qry.

Code:
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
Code:
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
 
At the risk of performance degradation:
Code:
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
 
Thank you. still coming up with the additional lines. but it ran quick
 
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
 
it should be.

a quick pivot table proved this. any other thougts?

I really appreciate your help
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top