Solved

# Top 10 for every type

Posted on 2007-10-16
185 Views
I have a table.
A            B            C
One            Paul            10
One            Paul            21
One            James            45
One            Tom            32
One            Smith            35
One            Mark            23
One            Mathew            12
One            Thomas            13
One            Henry            2
Two            Paul            43
Two            Paul            44
Two            James            23
Two            Tom            34
Two            Smith            34
Two            Mark            56
Two            Mathew            34
Two            Thomas            12
Two            Henry            45

I have a query
SELECT DISTINCTROW A, B, Sum(C) AS C
FROM Table
GROUP BY A, B;

I want the top 10 for every A .
i.e "one" should have top ten records, two, etc.

i.e A
should have
Paul            31
0
Question by:rishpat

LVL 19

Expert Comment

top 10 based on what criteria, sum of c?
0

LVL 4

Expert Comment

for this you need looping but in ms-access its not possible
for taking top 10 on all A. you can use this
SELECT top 10  A, B, Sum(C) AS C
FROM Table
GROUP BY A, B
for taking top 10 on particular one categry then
SELECT top 10   B, Sum(C) AS C
FROM Table
where A= "One"
GROUP BY  B
0

LVL 19

Expert Comment

jinda is correct, you'll have to create a recordset and loop through and insert results into a table.
0

Author Comment

ankush,

SELECT top 10  A, B, Sum(C) AS C
FROM Table
GROUP BY A, B

selects only the first 10 records... sorted by A
Top 10 should be based on sum(c)
0

Author Comment

ankush,

SELECT top 10  A, B, Sum(C) AS C
FROM Table
GROUP BY A, B

selects only the first 10 records... sorted by A
Top 10 should be based on sum(c)
0

LVL 19

Expert Comment

SELECT top 10   B, Sum(C) AS C
FROM Table
where A= "One"
GROUP BY  B
order by sum(c) desc

to get top 10 A
0

Author Comment

that still give the top 10 for only "One", how do I get all?
0

LVL 19

Expert Comment

you'll have to write a loop
0

Author Comment

and how do you do that?
0

LVL 19

Accepted Solution

copy this function into a standard module and execute it, replace any table names accordingly

Function fnLoop() As Boolean

Dim sql As String, d As DAO.Database,  rA As DAO.Recordset
Dim s As String

Set d = CurrentDb
s = "testTable" 'whatever table that gets deleted and recreated

'delete and create a new table
On Error Resume Next
DoCmd.DeleteObject acTable, s

On Error GoTo errH
sql = "SELECT a, b, sum(c) as SumC into " & s & "  FROM Table where 1 = 0 GROUP BY a, B "
d.Execute sql
sql = "select distinct a from table"  'note you called your table "table"
Set rA = d.OpenRecordset(sql)
If rA.RecordCount = 0 Then
Beep
MsgBox "you have no records!", vbExclamation
Set d = Nothing
Exit Function
End If

rA.MoveFirst

Do Until rA.EOF
sql = "insert into " & s
sql = sql & " SELECT top 10 a, b, sum(c) as SumC FROM Table where a ='" & rA!a _
& "' GROUP BY a, B  ORDER by sum(c) desc "
d.Execute sql
rA.MoveNext
Loop
'your table "testTable" is now populated with top 10 for each "A"

comExit:
Set d = Nothing
Exit Function

errH:

Beep
MsgBox Err & " " & Err.Description
Resume comExit

End Function
0

LVL 3

Expert Comment

Just Union 2 Select queries

SELECT Top 10 A, B, Sum(C) AS C
FROM Table
where A= "One"
GROUP BY  B
order by sum(c) desc

UNION

SELECT A, B, Sum(C) as C
FROM Table
WHERE A="Two"
GROUP BY  B
order by sum(c) desc
0

LVL 3

Expert Comment

Sorry, I miss something

SELECT Top 10 A, B, Sum(C) AS C
FROM Table
where A= "One"
GROUP BY  B
order by sum(c) desc

UNION

SELECT Top 10 A, B, Sum(C) as C
FROM Table
WHERE A="Two"
GROUP BY  B
order by sum(c) desc
0

LVL 19

Expert Comment

i'm assuming that there will be more than two "a"s otherwise if there are say 100 a's you'll need 100 union statements
0

Author Comment

Yes frankytee I have about 75 a's  cannot use Khoinggs solution!

0

LVL 19

Expert Comment

rishpat, did you try my function?
i'm about to log off so let me know how it went
0

LVL 19

Expert Comment

rishpat, have you tried it yet?
0

LVL 1

Expert Comment

Forced accept.

Computer101
Community Support Moderator
0

## Featured Post

### Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…