Filter top N results by field

[Secureuser.cm]

I have a sum query where I have 3 fields. DC which contains for example 10 DC location names. Qty field that is sum based and a cost field that is sum based and sorted in ascending order. When I filter the query through the top values for 25 I get the top 25 items by cost field. But I need top 25 by cost for each of the 10 DC field location names.




Can't figure out how to do this. Not sql proficient

[ranman256]

I have a 'report' table for my top X. It has 5 columns, name,amt, rankFld1,RankFld2,RankFld3
I append the 1st batch, the top 25 of Field1
then 2nd batch of top 25 of fld2
then fld3.

each of the 3 queries sums,and ranks the leaders.
then sum the report table to show the results.

[Secureuser.cm]

Not sure if that will work for me as the table has 8 million records. I'm running 5 back end data bases each with data for the month and year. Is there no other way to just query the it rather then making new tables?

[orange]

Post the SQL view of the latest query attempt.

See Top N in this allen Browne article

[Secureuser.cm]

Post the SQL view of the latest query attempt.

See Top N in this allen Browne article

This gives me the Top Items sorted by Cost for the Top 25. but I need the Top 25 sorted by cost for Each DC Field


SELECT [Q1 Inv Adj CC Query Reports Feb].DC, [Q1 Inv Adj CC Query Reports Feb].Facility, [Q1 Inv Adj CC Query Reports Feb].Item, [Q1 Inv Adj CC Query Reports Feb].Description, Sum([Q1 Inv Adj CC Query Reports Feb].[Inv Adj Qty]) AS [SumOfInv Adj Qty], Sum([Q1 Inv Adj CC Query Reports Feb].[Total Cost]) AS [SumOfTotal Cost]
FROM [Q1 Inv Adj CC Query Reports Feb]
GROUP BY [Q1 Inv Adj CC Query Reports Feb].DC, [Q1 Inv Adj CC Query Reports Feb].Facility, [Q1 Inv Adj CC Query Reports Feb].Item, [Q1 Inv Adj CC Query Reports Feb].Description
HAVING (((Sum([Q1 Inv Adj CC Query Reports Feb].[Total Cost]))<0))
ORDER BY Sum([Q1 Inv Adj CC Query Reports Feb].[Total Cost]);

[orange]

How does this give top 25?

I gave you a link to subqueries and Top N as example. You need to set up logic/grouping to match your needs using the concept/approach from the article.

Can you post a sample of your data as excel or csv?

[Secureuser.cm]

Sorry I attached the wrong SQL

SELECT TOP 25 [Q1 Inv Adj CC Query Reports Feb].DC, [Q1 Inv Adj CC Query Reports Feb].Facility, [Q1 Inv Adj CC Query Reports Feb].Item, [Q1 Inv Adj CC Query Reports Feb].Description, Sum([Q1 Inv Adj CC Query Reports Feb].[Inv Adj Qty]) AS [SumOfInv Adj Qty], Sum([Q1 Inv Adj CC Query Reports Feb].[Total Cost]) AS [SumOfTotal Cost]
FROM [Q1 Inv Adj CC Query Reports Feb]
GROUP BY [Q1 Inv Adj CC Query Reports Feb].DC, [Q1 Inv Adj CC Query Reports Feb].Facility, [Q1 Inv Adj CC Query Reports Feb].Item, [Q1 Inv Adj CC Query Reports Feb].Description
HAVING (((Sum([Q1 Inv Adj CC Query Reports Feb].[Total Cost]))<0))
ORDER BY Sum([Q1 Inv Adj CC Query Reports Feb].[Total Cost]);

[orange]

That makes more sense.
Can you post a sample of your data as excel or csv? 20-40 records

[June7]

Did you review the Top N Records Per Group section in Allen's article?

[Secureuser.cm]

Network CC Sum N Query Feb.zip

Here is a sample of the data. the actual query returns over 100k records. I did read the attached file but I understand that I need to do a Subquery in SQL but I have not learned SQL yet I am Self taught and do everything in Design View.

[June7]

Also mostly self-taught. I learned SQL by example. I followed Allen's example and adjusted for my tables/fields.

Will need a unique identifier field - autonumber will serve. Based on the posted Excel, do this test:

SELECT * FROM Sheet1 WHERE ID IN (SELECT TOP 5 ID FROM Sheet1 AS Dupe WHERE Dupe.DC=Sheet1.DC ORDER BY Dupe.DC, Dupe.[SumOfTotal Cost] DESC) ORDER BY DC, [SumOfTotal Cost] DESC;

However, all the numbers are negative so maybe you want ASC sort order.

[orange]

Are your data values all negative, really?
I agree with June's sql

I reformatted a little as

SELECT *
FROM Network_cc_sum_N_query_Feb
WHERE ID IN (
        SELECT TOP 5 ID
        FROM Network_cc_sum_N_query_Feb AS Dupe
        WHERE Dupe.DC = Network_cc_sum_N_query_Feb.DC
        ORDER BY Dupe.[SumOfTotal Cost] DESC
                       ,DC
        )
ORDER BY DC
    ,[SumOfTotal Cost] DESC;

[Secureuser.cm]

I have a query for Neg numbers and a query for Pos numbers

[orange]

????Why???

It is easier for readers to respond with focus when they have a clear understanding of WHAT you are trying to do. This is often most communicative if it is plain English - no jargon, no attempt to quasi-code something. The readers are human volunteers- no special powers, not clairvoyant.

Why are there 2 sets of values -- positive and negative?
Would you consider 400 queries if you had 400 employees?

Did you try the SQL/query that was provided? Results??

[Secureuser.cm]

Are your data values all negative, really?
I agree with June's sql

I reformatted a little as

SELECT *
FROM Network_cc_sum_N_query_Feb
WHERE ID IN (
        SELECT TOP 5 ID
        FROM Network_cc_sum_N_query_Feb AS Dupe
        WHERE Dupe.DC = Network_cc_sum_N_query_Feb.DC
        ORDER BY Dupe.[SumOfTotal Cost] DESC
                       ,DC
        )
ORDER BY DC
    ,[SumOfTotal Cost] DESC;

getting error saying can not locate query or table,, I found it added ; to the SQL statement when I pasted it. I removed and it is asking me to input a criteria in a pop up box for ID?