Find Top n values in each Name

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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

;




****************************
 
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.
 
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


**********************
 
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;
 
Back
Top