[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Top 10 for every type

Posted on 2007-10-16
18
Medium Priority
?
189 Views
Last Modified: 2010-03-20
I have a table.
A            B            C                                   
One            Paul            10
One            Paul            21
One            James            45
One            Tom            32
One            Smith            35
One            Maddox            22
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            Maddox            45
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
Comment
Question by:rishpat
  • 8
  • 5
  • 2
  • +2
17 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 20084763
top 10 based on what criteria, sum of c?
0
 
LVL 4

Expert Comment

by:jindalankush
ID: 20084765
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

by:frankytee
ID: 20084904
jinda is correct, you'll have to create a recordset and loop through and insert results into a table.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:rishpat
ID: 20085136
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

by:rishpat
ID: 20085138
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

by:frankytee
ID: 20085187
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

by:rishpat
ID: 20085555
that still give the top 10 for only "One", how do I get all?
0
 
LVL 19

Expert Comment

by:frankytee
ID: 20085657
you'll have to write a loop
0
 

Author Comment

by:rishpat
ID: 20085798
and how do you do that?
0
 
LVL 19

Accepted Solution

by:
frankytee earned 2000 total points
ID: 20085867
backup your db first!!!
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

by:KhoiNqq
ID: 20086009
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

by:KhoiNqq
ID: 20086013
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

by:frankytee
ID: 20086259
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

by:rishpat
ID: 20086291
Yes frankytee I have about 75 a's  cannot use Khoinggs solution!

0
 
LVL 19

Expert Comment

by:frankytee
ID: 20086329
rishpat, did you try my function?
i'm about to log off so let me know how it went
0
 
LVL 19

Expert Comment

by:frankytee
ID: 20118152
rishpat, have you tried it yet?
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20953123
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question