Find Top n values in each Name

[Guest]

Hi all

I would like to find some TOP 3 of each Product like this following

Data Table

Name Qty_1 Qty_2 Qty_3
A 6 12 20
A 5 1 45.6
A 5 2 16.7
A 5 7 49.6
A 1 11 15.4
A 2 1 12.1
B 8 5 9
B 2 0 10
B 7 10 18
B 6 2 10
B 6 3 31
B 1 0 10
B 8 5 9
B 11 4 23
C 7 5 9
D 5 11 5
E 15 15 15
E 10 15 15
E 6 13 10
E 6 2 36.8
F 10 9 23
G 10 4 10
G 12 8 42
H 6 5 16
H 2 6 7
H 14 7 2
H 8 6 7
I 9 1 11

Required Results Type #1
Name Qty_1 Qty_2 Qty_3
A 6 12 20
A 5 7 49.6
A 5 2 16.7
B 11 4 23
B 8 5 9
B 8 5 9
C 7 5 9
D 5 11 5
E 15 15 15
E 10 15 15
E 6 13 10
F 10 9 23
G 10 4 10
G 12 8 42
H 14 7 2
H 8 6 7
H 6 5 16
I 9 1 11

Required Results Type #2
Name Qty_1 Qty_2 Qty_3
A 6 12 20
A 5 7 49.6
A 5 2 16.7
A 5 1 45.6
A 2 1 12.1
B 11 4 23
B 8 5 9
B 8 5 9
B 7 10 18
C 7 5 9
D 5 11 5
E 15 15 15
E 10 15 15
E 6 13 10
E 6 2 36.8
F 10 9 23
G 12 8 42
G 10 4 10
H 14 7 2
H 8 6 7
H 6 5 16
I 9 1 11

Now, I try to use

SELECT TOP 3 Name, Qty_1, Qty_2, Qty_3
FROM Table1
GROUP BY Name, Qty_1, Qty_2, Qty_3
ORDER BY Qty_3 DESC;

But the result have showed only the first 3 records of Name-A
How can I solve this problem?

Thanks in advance

 

[Van T. Dinh]

Try The Microsoft Knowledge Base:

http://support.microsoft.com/?id=210039

 

[Guest]

Hi
I try to use this statement

SELECT Name,Qty_1,Qty_2,Qty_3
FROM Table1 as S
WHERE Name in
(SELECT TOP 3 T.Name
FROM Table1 as T
WHERE T.Name = S.Name
ORDER BY T.Qty_1 DESC)
And it shows as these following that is not my required results.

Name Qty_1 Qty_2 Qty_3
A 5 1 45.6
A 5 2 16.7
A 2 1 12.1
B 2 0 10
B 8 5 9
C 7 5 9
D 5 11 5
E 6 13 10
E 10 15 15
F 10 9 23
G 12 8 42
G 10 4 10
H 8 6 7
H 2 6 7
H 6 5 16
I 9 1 11
A 6 12 20
A 5 7 49.6
A 1 11 15.4
B 1 0 10
B 8 5 9
B 11 4 23
B 7 10 18
B 6 3 31
B 6 2 10
E 15 15 15
E 6 2 36.8
H 14 7 2

Please advise me for a solution too.

Thanks

;




****************************

 

[Van T. Dinh]

SELECT Main.Name,Main.Qty_1,Main.Qty_2,Main.Qty_3
FROM Table1 as Main
WHERE Main.Qty_1 in
(SELECT TOP 3 Sub.Qty_1
FROM Table1 as Sub
WHERE Sub.Name = Main.Name
ORDER BY Sub.Qty_1 DESC)

Note that you may end up with more than 3 rows per name, e.g. if there are 2
or more Records for the same name with Qty_1 ranked equal third.

"Name" is a bad Field name since evey object in Access has the Property
"Name". Suggest you change the Field name to something else.

 

[Guest]

Mr. Van T. Dinh
Thank you for your reply. Actually, i would like to select the top 3 value
of quantity of each vendor or each plant location. Thus, the Name is the
sample field for trial my sql statement.
After i have tried the below statement, the result is shown as these
following

SELECT S.Vendor, S.Qty_1, Qty_2, Qty_3
FROM Table1 AS S
WHERE S.Qty_1 in
(SELECT TOP 3 T.Qty_1
FROM Table1 as T
WHERE T.Vendor = S.Vendor
ORDER BY T.Qty_1 DESC)
ORDER BY S.Vendor, S.Qty_1 DESC , S.Qty_2 DESC;

**********************
Vendor Qty_1 Qty_2 Qty_3
A 6 12 20
A 5 7 49.6
A 5 2 16.7
A 5 1 45.6
B 11 4 23
B 8 5 9
B 8 5 9
C 7 5 9
D 5 11 5
E 15 15 15
E 10 15 15
E 6 13 10
E 6 2 36.8
F 10 9 23
G 12 8 42
G 10 4 10
H 14 7 2
H 8 6 7
H 6 5 16
I 9 1 11

For Vedor A, it shows only the top 3 records (but top 2 value) and counts
the other one of top 2 value. It is not my requirement ( I would like to
select the top 3 values
same as results type #2)

Name Qty_1 Qty_2 Qty_3
A 6 12 20
A 5 7 49.6
A 5 2 16.7
A 5 1 45.6
A 2 1 12.1


Thanks again
Gogzilla


**********************

 

[Van T. Dinh]

Try:

SELECT S.Vendor, S.Qty_1, Qty_2, Qty_3
FROM Table1 AS S
WHERE S.Qty_1 in
(SELECT DISTINCT TOP 3 T.Qty_1
FROM Table1 as T
WHERE T.Vendor = S.Vendor
ORDER BY T.Qty_1 DESC)
ORDER BY S.Vendor, S.Qty_1 DESC , S.Qty_2 DESC;

 

[Guest]

Mr. Van T. Dinh

Thanks a lot for all your help
Gogzilla