Select Top 5 For Each Group By Value 2

[mordja]

Hi,

I have three fields: Currency, Cusip and Balance. I group by Currency and Cusip and sum the balance.
I want to select the top 5 cusips and their respective balances per currency. Can you do several Top 5's in the one sql statement without having to do a union and a whole lot of select statements( the currency field has over 30 values )?

Something like

Select Top 5 A.Currency, A.Cusip, Sum(A.Balance) as Total
From TableName as A,(Select Distinct Currecncy From Tablename) as B
Where A.Currency = B.Currency
Group By A.Currency, A.Cusip

I know that is not correct but Im not where to start or If it can be done in SQL.

Any thoughts?

Thanks

Mordja

 

[NorthNone]

What you have seemed to work for me once I corrected "Currecncy" to "Currency"

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[DotNetGnat]

yes try something like this:

Select A.Currency, A.Cusip, Sum(A.Balance) as Total
FROM TableName A WHERE A.Cusip in (SELECT TOP 5 Cusip FROM TableName B WHERE A.Currency=B.Currency ORDER BY Cusip DESC)

-DNG

 

[DotNetGnat]

add group by clause in the end...

-DNG

 

[mordja]

NorthNone,

What I had only brought back the top five overall, I want the top five per currency, with 30 currencys that makes 150 rows returned.

DotNetGnat,

Tried that but it didnt work, A.Currency is not recognised in the second select statement prompting me for a value.

Ive written a vba procedure which loops through the currency set passing the currency as a paremeter running the top 5 and appending the results to a table. It would be nice to if you can do it in a single SQL statement.

Thanks

Mordja

 

[PHV]

And something like this ?
SELECT A.Currency, A.Cusip, Sum(A.Balance) as Total
FROM TableName AS A
WHERE A.Cusip In (SELECT TOP 5 Cusip
FROM TableName WHERE [Currency]=A.Currency GROUP BY Cusip ORDER BY Sum(Balance) DESC)
GROUP BY A.Currency, A.Cusip
ORDER BY 1, 3 DESC;

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

 

[mordja]

PHV,

That will prompt me for the currency and will only result in the top 5 for a singular currency, I want the top five cusips by balance for each currency.

Currency Cusip Balance
AUL AX0903 19000000
AUL AX1203 17500000
AUL AX4203 11000000
AUL AX0303 9000000
AUL AX1203 5600000
CAD CX1903 21003200
CAD CX0923 19000000
CAD CX0953 17000000
CAD CX3903 12000000
CAD CX2903 11500000
EUR AX0903 39000000
EUR AX0903 29000000
EUR AX0903 25000000
EUR AX0903 22000000
EUR AX0903 20000000

etc

Thanks

Mordja

 

[PHV]

BTW, which version of Access ?
What is the REAL SQL code you tried ?

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

 

[DotNetGnat]

recheck for typos...

-DNG

 

[mordja]

PHV,

I tried a lot of different things but could only get the Top 5 records back as opposed to the numberOfCurrency * 5.

SELECT TOP 5 A.currency, A.cusip, Sum(A.BorrowBalance) AS SumOfBorrowBalance
FROM Borrows_quniAll AS A, (Select Distinct currencyfrom Borrows_quniAll) as B
Where A.currency= b.currency
GROUP BY A.currency, A.cusip
ORDER BY Sum(A.BorrowBalance) DESC
;

The above obviously doesnt work as it just limits the countryOfIssue to itself.

I cant see how you can iterate through the currency set and select the top five cusips by balance per currency. As I said Ive gotten around it using

Public Function createCusipCommentary(ByVal strSourceTable As String, ByVal strWriteTable, ByVal strAppendQuery)
On Error GoTo Err_logError

Dim rstCurrency As ADODB.Recordset
Dim qdf As DAO.QueryDef
Dim strReturn As String
DoCmd.SetWarnings False

Set rstCurrency = New ADODB.Recordset
rstCurrency .ActiveConnection = CurrentProject.Connection
rstCurrency .Open "Select Distinct currency from " & strSourceTable & ";"

DoCmd.RunSQL "Delete * From " & strWriteTable
Set qdf = CurrentDb.QueryDefs(strAppendQuery)

With rstCurrency 
    While Not .EOF
        qdf.Parameters("[EnterCurrency]") = .Fields(0)
        qdf.Execute
        .MoveNext
    Wend
End With

DoCmd.SetWarnings True

Exit_logError:
    rstCurrency .Close
    qdf.Close
    Set qdf = Nothing
    Set rstCurrency = Nothing
    Exit Function
    
Err_logError:
    MsgBox "Error In ErrorLog.logError () :" & Err.Description
    Resume Exit_logError
    
End Function

The above passes the currency to an append query which appends the top 5 by currency to strWriteTable.
However I still would like to know if it can be done in SQL. Im using access 2003.

Thanks for your assistance.

Mordja

 

[PHV]

You didn't answer my previous questions.

 

[mordja]

PHV,

You asked

"BTW, which version of Access ?
What is the REAL SQL code you tried ? "

I replied

"I tried a lot of different things but could only get the Top 5 records back as opposed to the numberOfCurrency * 5.

SELECT TOP 5 A.currency, A.cusip, Sum(A.BorrowBalance) AS SumOfBorrowBalance
FROM Borrows_quniAll AS A, (Select Distinct currencyfrom Borrows_quniAll) as B
Where A.currency= b.currency
GROUP BY A.currency, A.cusip
ORDER BY Sum(A.BorrowBalance) DESC
;

The above obviously doesnt work as it just limits the countryOfIssue to itself.

And

Im using access 2003.

As to the REAL sql I have tried as I said I kept trying a lot of different things including all the response that I received. The above is the only REAL attempt that I had saved before I became frustrated and resorted to VBA.

Im not try to be difficult or ask anyone for the exact solution, it is easy enough to do in VBA although I expect a little slower, but was hoping that someone could give me a clear yes/no answer with an example as to whether it is possible to do an iterative top 5 statement in in SQL.

Thanks for your help

Mordja

 

[mordja]

PHV,

Apologies, my last real SQL attempt is

SELECT A.countryOfIssue, A.Cusip, Sum(A.BorrowBalance) AS SumOfBorrowBalance
FROM Borrows_quniAll AS A, (SELECT TOP 5 C.countryOfIssue, C.cusip, Sum(C.BorrowBalance) AS SumOfBorrowBalance
FROM Borrows_quniAll AS C
GROUP BY C.countryOfIssue, C.cusip
ORDER BY Sum(C.BorrowBalance) DESC) AS B
WHERE B.countryOfIssue = A.countryOfIssue AND B.Cusip = A.Cusip
GROUP BY A.countryOfIssue, A.Cusip
ORDER BY A.countryOfIssue, Sum(A.BorrowBalance) DESC;
[code]

CountryOfIssue = Currency and BorrowBalance = Balance.

Thanks

Mordja

 

[NorthNone]

I don't have an answer, but have been watching this thread with interest. How many times have I settled for one solution simply because it works when I should have taken the time to explore alternates! This site rocks!


---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[PHV]

My suggestion was:
SELECT A.countryOfIssue, A.Cusip, Sum(A.BorrowBalance) AS SumOfBorrowBalance
FROM Borrows_quniAll AS A
WHERE A.Cusip In (SELECT TOP 5 Cusip
FROM Borrows_quniAll WHERE countryOfIssue=A.countryOfIssue GROUP BY Cusip ORDER BY Sum(BorrowBalance) DESC)
GROUP BY A.countryOfIssue, A.Cusip
ORDER BY 1, 3 DESC;

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

 

[mordja]

PHV,

Thanks, that works. Although if there are several cusips with the same balance then the top five will return five balances but possibly more than five cusips.

Mordja