SQL Need to select Top 5 in each division

[drmichaelw]

I have a table with three columns: TOTAL, PART, DIVISION.
It is sorted by DIVISION then TOTAL.

I need to create a results table that returns the TOP 5 occuring parts and their respective totals for each division listed in the table. I need to order the results by DIVISION then by TOTAL .

Thank you in advance for your help
MW

 

[Poduska]

Try Microsoft Knowedge base article 210039

http://support.microsoft.com.default.aspx?scid=kb;en-us;210039

This was on the page I printed. the title is "ACC2000: how to crate a "Top N values" per group" Query"

It worked for me but is a slow query.

 

[drmichaelw]

If the article is on the link you gave me, I can't display the page. Do you have any other way of me getting this article?

 

[Poduska]

Just found it again. Perhaps I cannot type!. I also pasted the entire article but it will be easier to read on the site. Here is the link I cut from my browser. goodluck

http://support.microsoft.com/default.aspx?scid=kb;en-us;210039

ACC2000: How to Create a "Top N Values Per Group" Query
The information in this article applies to:
Microsoft Access 2000

This article was previously published under Q210039
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).


SUMMARY
This article shows you two methods that you can use to create queries that list only the top n items per group. By using either one of these methods, you can create a query that displays only the top five salespeople for each region.
MORE INFORMATION
To list only the top n items within a group in a query, you must specify a criteria that dynamically reads the grouping column in the query and limits the item column to the top n values within each group.

Method 1 uses a SQL subquery to dynamically generate a list of the top n items for each group, and then uses this list as the criteria for the item column by using the In operator.

Method 2 uses a user-defined function to return the nth item within a specific group, which is then used with the >= operator to return the nth-and-greater items.
Method 1
Follow these steps to create a query in the Northwind sample database that displays the top three UnitsInStock per CategoryID. The query uses a SQL subquery, which returns the top three UnitsInStock, given a specific CategoryID, and then uses the In operator to limit the records in the main query.

NOTE: In the criteria example in step 5, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating the criteria.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.


Open the sample database Northwind.mdb.
Click Queries, and then click New.
Click Design View, and then click OK.
In the Show Table dialog box, click Categories and Products, click Add, and then click Close.
Add the following fields to the query grid:
Field: CategoryName
Sort: Ascending

Field: ProductName

Field: UnitsInStock
Sort: Descending
Criteria: In (Select Top 3 [UnitsInStock] From Products Where _
[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)

Run the query.
Note that the query returns the top three UnitsInStock for each category.
Method 2
Follow these steps to create a query in the Northwind sample database that displays the last five orders placed per customer. The query uses a criteria function called NthInGroup() that returns the fifth most recent OrderDate, given a specific CustomerID. If you use the >= operator along with this function, all orders that were placed on or after the fifth most recent OrderDate for each customer will be returned.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.


Open the sample database Northwind.mdb.
Create a new module, and then add the following line to the Declarations section if it is not already there:
Option Explicit

Create the following procedure:
Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As DATABASE

If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
Else
' Set the SQL statement parameters. These are the only items
' that need to be customized in this function.
' Set to Item field name.
ItemName = "OrderDate"
' Set to Group ID field name.
GroupIDName = "CustomerID"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "Orders"
' Build a Top N SQL statement dynamically given N and
' GroupID as parameters. Note that the sort is by the
' item in descending order, in order to get the Top N
' largest items.
SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC & " "
SQL = SQL & "Order By [" & ItemName & "] Desc"
' Open up recordset on Top N SQL statement and read the
' last record to get the smallest item in the Top N.
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function

Compile the module, and then close and save the module as basTopN.
Click Queries, and then click New.
Click Design View, and then click OK.
In the Show Table dialog box, click Customers and Orders, click Add, and then click Close.
Add the following fields to the query grid:
Field: CustomerID
Sort: Ascending

Field: OrderID

Field: Order Date
Sort: Descending
Criteria: >= NthInGroup([Customers].[CustomerID],5)

Run the query.
Note that for all customers who have at least five orders, the query returns the five most recent orders. For customers with fewer than five orders, the query returns all orders.
REFERENCES
For more information about subqueries, click Microsoft Access Help on the Help menu, type sql subqueries in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Last Reviewed: 8/6/2002
Keywords: kbhowto kbinfo KB210039

 

[drmichaelw]

Found the article, Thanks

I am trying to run the query. How slow did your query run.
My is taking forever

 

[Poduska]

Its been a while but I ran it against a local table of say 4500 lines and say 10 min. Not sure but it was so long that it would not be a viable option for my oracle tables which reach 60 to 90 mm lines. Try on a small sample database. good luck

 

[drmichaelw]

unfortunatly method 1 didn't work.
The query was running in access for over 20 mins and nothing was happening. do you have any other suggestion before I repost this challenge.