Complex query - TOP 3

  • Thread starter Thread starter Kamyk
  • Start date Start date
K

Kamyk

Hello all!

I have the following problem.
I would like to create a query, which will sum up 3 orders of highest
quantity for each department.
Here is the visual example of my question:

DEPT ORDER QUANTITY
Stamping 11111111111 40
Stamping 22222222222 30
Stamping 66666666666 10
Assembly 55555555555 90
Assembly 99999999999 80
Assembly 33333333333 70

Source data looks like this:

DEPT ORDER QUANTITY
Stamping 11111111111 40
Stamping 55555555555 4
Stamping 22222222222 30
Stamping 66666666666 10
Stamping 33333333333 2
Assembly 11111111111 3
Assembly 55555555555 90
Assembly 99999999999 80
Assembly 33333333333 70
Assembly 66666666666 40

I can also write you that the source data mentioned above are the result of
processing (I mean it is a query).
Can you help me to achieve such query?

Thanx in advanced for you help
Marcin from Poland
 
How about:

Select T.Dept, T.Order, T.Quantity, DCOUNT("Quantity", "yourTable", "Dept =
""" & T.Dept """ AND Quantity >= T.Quantity)
FROM yourTable
WHERE DCOUNT("Quantity", "yourTable", "Dept = """ & T.Dept & """ AND
Quantity >= T.Quantity) < = 3

This could return less than 3 rows for each dept if there is more than one
order that has the same quantity, and this would quantity would rank in the
top 3.

HTH
Dale
 
I THINK that the following untested idea may work. It will probably be slow.

SELECT A.Dept, A.Order, A.Quantity
FROM SomeTable as A
WHERE A.Quantity IN
(SELECT TOP 3 B.Quantity
FROM SomeTable as B
WHERE B.Dept = A.Dept
ORDER BY B.Quantity DESC,
B.Order)

This will eliminate ties, so if you want to return ties, remove B.Order from the
sort in the sub-query.
 
Back
Top