Top 10 for every type
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
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
frankytee
top 10 based on what criteria, sum of c?
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
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
jinda is correct, you'll have to create a recordset and loop through and insert results into a table.
ASKER
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)
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)
ASKER
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)
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)
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
FROM Table
where A= "One"
GROUP BY B
order by sum(c) desc
to get top 10 A
ASKER
that still give the top 10 for only "One", how do I get all?
you'll have to write a loop
ASKER
and how do you do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
ASKER
Yes frankytee I have about 75 a's cannot use Khoinggs solution!
rishpat, did you try my function?
i'm about to log off so let me know how it went
i'm about to log off so let me know how it went
rishpat, have you tried it yet?
Forced accept.
Computer101
Community Support Moderator
Computer101
Community Support Moderator