Return Top 5 and Bottom 5

[Guest]

I want to return the top 5 and bottom 5 days of production by product. Some
products' production numbers are recorded in Pieces and some are recorded in
Stacks. Can I do this with one query?

 

[Allen Browne]

If the quantity was in a single field, you could use a UNION query, e.g.:

SELECT TOP 5 Table1.* FROM Table1 ORDER BY Stacks, ID
UNION
SELECT TOP 5 Table1.* FROM Table1 ORDER BY Stacks DESC, ID DESC;

You will need to modify that to handle both fields, after deciding how you
define the TOP 5. Presumably there is some kind of proportion where 1000
pieces = 1 stack or something, and the records have one or the other (not
neither, and not both), so you would use an IIf() expression to convert them
to something that works for both.

 

[Guest]

Is there a way to get the top 5 best days of production (quantity column) for
each product. So if there were 5 different products, it would return the best
5 days of each? Thanks!

 

[Allen Browne]

This will involved writing a subquery.

To get you started, see:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

 

[Guest]

That looks like what I need to do...can I return the top 5 days of production
and the bottom 5 days of production in the same query or will I have to have
two separate queries? Thank you for your help

 

[Allen Browne]

2 subqueries.

Depending how comfortable you are with SQL, and what else is going on in
these queries, it may be possible to combine them into one query.

Really rough example (not based on your fields):
SELECT ID
FROM Table1
WHERE Table1.ID IN
(SELECT TOP 5 Dupe.ID
FROM Table1 AS Dupe
ORDER BY Dupe.ID)
OR Table1.ID IN
(SELECT TOP 5 Dupe.ID
FROM Table1 AS Dupe
ORDER BY Dupe.ID DESC);