display top 5 records for each subcategory

L

lay

I need to know how to return just the top 5 DUs (Based on sum of cases lost)
for each Code desc. Below is my current SQL that returns back all records for
all CodeDesc

Please help? Thanks!



SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown -
HPCStockAvail].CasesLost) AS SumOfCasesLost
FROM [(0005a1)InternalDrilldown - HPCStockAvail]
GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description
ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
 
P

pietlinden

I need to know how to return just the top 5 DUs (Based on sum of cases lost)
for each Code desc. Below is my current SQL that returns back all recordsfor
all CodeDesc

Please help? Thanks!

SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown -
HPCStockAvail].CasesLost) AS SumOfCasesLost
FROM [(0005a1)InternalDrilldown - HPCStockAvail]
GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description
ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
Click to expand...

use the TOP VALUES predicate.

SELECT TOP 5 [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,...
 
K

KARL DEWEY

Try this --
SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM [(0005a1)InternalDrilldown
- HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]) AS Rank
FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q
WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]))<=5))
ORDER BY Q.DU, Q.CasesLost DESC;
 
L

lay

What does the Q represent? Do I substitute it for the table name?

I pasted the whole string (as is) into my current SQL, right before the
"FROM" and I got the error message: "The SELECT statement includes a reserved
word or an argument name that is misspelled or missing, or the punctuation is
incorrect"

Sorry, i'm new at SQL

KARL DEWEY said:
Try this --
SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM [(0005a1)InternalDrilldown
- HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]) AS Rank
FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q
WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]))<=5))
ORDER BY Q.DU, Q.CasesLost DESC;

--
KARL DEWEY
Build a little - Test a little


lay said:
I need to know how to return just the top 5 DUs (Based on sum of cases lost)
for each Code desc. Below is my current SQL that returns back all records for
all CodeDesc

Please help? Thanks!



SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown -
HPCStockAvail].CasesLost) AS SumOfCasesLost
FROM [(0005a1)InternalDrilldown - HPCStockAvail]
GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description
ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
Click to expand...
Click to expand...
 
L

lay

Thanks. I've tried that before, but all it does is return the top 5 values
for the whole record set. I need it to be the top 5 (or less) values of each
subcategory in the record set.

[email protected] said:
I need to know how to return just the top 5 DUs (Based on sum of cases lost)
for each Code desc. Below is my current SQL that returns back all records for
all CodeDesc

Please help? Thanks!

SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown -
HPCStockAvail].CasesLost) AS SumOfCasesLost
FROM [(0005a1)InternalDrilldown - HPCStockAvail]
GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description
ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
Click to expand...

use the TOP VALUES predicate.

SELECT TOP 5 [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,...
Click to expand...
 
L

lay

Never mind. Please disregard the post above - I just needed my coffee before
attempting to dissect any SQL strings!

The reason why the error message popped up is because Q1 was never defined -
the "AS" was missing. Once i popped that in, it runs fine...although
extremely slowly.

Thanks for your help!

lay said:
What does the Q represent? Do I substitute it for the table name?

I pasted the whole string (as is) into my current SQL, right before the
"FROM" and I got the error message: "The SELECT statement includes a reserved
word or an argument name that is misspelled or missing, or the punctuation is
incorrect"

Sorry, i'm new at SQL

KARL DEWEY said:
Try this --
SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM [(0005a1)InternalDrilldown
- HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]) AS Rank
FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q
WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown - HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]))<=5))
ORDER BY Q.DU, Q.CasesLost DESC;

--
KARL DEWEY
Build a little - Test a little


lay said:
I need to know how to return just the top 5 DUs (Based on sum of cases lost)
for each Code desc. Below is my current SQL that returns back all records for
all CodeDesc

Please help? Thanks!



SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown -
HPCStockAvail].CasesLost) AS SumOfCasesLost
FROM [(0005a1)InternalDrilldown - HPCStockAvail]
GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU, [(0005a1)InternalDrilldown -
HPCStockAvail].Description
ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost) DESC;
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

To improve speed, you can try:



lay said:
Never mind. Please disregard the post above - I just needed my coffee
before
attempting to dissect any SQL strings!

The reason why the error message popped up is because Q1 was never
defined -
the "AS" was missing. Once i popped that in, it runs fine...although
extremely slowly.

Thanks for your help!

lay said:
What does the Q represent? Do I substitute it for the table name?

I pasted the whole string (as is) into my current SQL, right before the
"FROM" and I got the error message: "The SELECT statement includes a
reserved
word or an argument name that is misspelled or missing, or the
punctuation is
incorrect"

Sorry, i'm new at SQL

KARL DEWEY said:
Try this --
SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM
[(0005a1)InternalDrilldown
- HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]) AS Rank
FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q
WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown -
HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]))<=5))
ORDER BY Q.DU, Q.CasesLost DESC;

--
KARL DEWEY
Build a little - Test a little


:

I need to know how to return just the top 5 DUs (Based on sum of
cases lost)
for each Code desc. Below is my current SQL that returns back all
records for
all CodeDesc

Please help? Thanks!



SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU,
[(0005a1)InternalDrilldown -
HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown -
HPCStockAvail].CasesLost) AS SumOfCasesLost
FROM [(0005a1)InternalDrilldown - HPCStockAvail]
GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU,
[(0005a1)InternalDrilldown -
HPCStockAvail].Description
ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost)
DESC;
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

To increase speed, you can try:


SELECT Q.du, Q.casesLost
FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q
INNER JOIN [(0005a1)InternalDrilldown - HPCStockAvail] AS Q1
ON Q.du = Q1.du AND Q.casesLost <= Q1.casesLost
GROUP BY Q.du, Q.casesLost
HAVING COUNT(*) <= 5





You can even improve the speed further if you can make a temp table of your
query [(0005a1)InternalDrilldown - HPCStockAvail], and index fields du and
casesLost (you cannot add indexes to a query, that is why you need a temp
table).






You can even speed up furthermore the whole thing by using a temporary table
and an intermediate query, and some extra work:


have a table temp, empty, with 3 fields: an autonumber, au, the primary key,
plus the two fields du, and casesLost. Append the data to temp from your
query with:

INSERT INTO temp(du, casesLost)
SELECT du, casesLost FROM [(0005a1)InternalDrilldown - HPCStockAvail]
ORDER BY du, casesLost


The autonumber will automatically be filled.

then:

SELECT du, MIN(au) AS myMin
FROM temp
GROUP BY du

as saved query qmin,


return the autonumber value which starts each sequence, so

finally


SELECT a.du, a.casesLost, a.au-b.myMin + 1 AS rank
FROM temp AS a INNER JOIN qmin AS b
ON a.du = b.du AND a.au <= 5=b.myMin



should return the desired values.


Note that you have to repeat the sequence: empty table temp, append data,
run the last query

each time some data changed. Maybe better doing that with a VBA subroutine
rather than calling these steps, manually.




Vanderghast, Access MVP



lay said:
Never mind. Please disregard the post above - I just needed my coffee
before
attempting to dissect any SQL strings!

The reason why the error message popped up is because Q1 was never
defined -
the "AS" was missing. Once i popped that in, it runs fine...although
extremely slowly.

Thanks for your help!

lay said:
What does the Q represent? Do I substitute it for the table name?

I pasted the whole string (as is) into my current SQL, right before the
"FROM" and I got the error message: "The SELECT statement includes a
reserved
word or an argument name that is misspelled or missing, or the
punctuation is
incorrect"

Sorry, i'm new at SQL

KARL DEWEY said:
Try this --
SELECT Q.DU, Q.CasesLost, (SELECT COUNT(*) FROM
[(0005a1)InternalDrilldown
- HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]) AS Rank
FROM [(0005a1)InternalDrilldown - HPCStockAvail] AS Q
WHERE ((((SELECT COUNT(*) FROM [(0005a1)InternalDrilldown -
HPCStockAvail] Q1
WHERE Q1.[DU] = Q.[DU]
AND Q1.[CasesLost] >= Q.[CasesLost]))<=5))
ORDER BY Q.DU, Q.CasesLost DESC;

--
KARL DEWEY
Build a little - Test a little


:

I need to know how to return just the top 5 DUs (Based on sum of
cases lost)
for each Code desc. Below is my current SQL that returns back all
records for
all CodeDesc

Please help? Thanks!



SELECT [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU,
[(0005a1)InternalDrilldown -
HPCStockAvail].Description, Sum([(0005a1)InternalDrilldown -
HPCStockAvail].CasesLost) AS SumOfCasesLost
FROM [(0005a1)InternalDrilldown - HPCStockAvail]
GROUP BY [(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat,
[(0005a1)InternalDrilldown - HPCStockAvail].CCFOT_SubCat_Description,
[(0005a1)InternalDrilldown - HPCStockAvail].CutDelayCode,
[(0005a1)InternalDrilldown - HPCStockAvail].CodeDesc,
[(0005a1)InternalDrilldown - HPCStockAvail].DU,
[(0005a1)InternalDrilldown -
HPCStockAvail].Description
ORDER BY Sum([(0005a1)InternalDrilldown - HPCStockAvail].CasesLost)
DESC;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Top 5 3
top 20 records by group 5
Select Top with aggregate function 1
Select Top 5 8
Return Top 10 Record 6
Using the TOP argument 1
Selecting top records based on multiple fields 3
Top 5 Query Question 2

Top