Select top N by group problem

[linzeyd]

I have a problem that's driving me crazy. i have a table that is a series of things like this

(Unit type) (part description)  (count)
IWU   High pressure flowmeter      3
IWU   Transformer                  2
IWU   Fender                       2
GEN   Fender                       2
GEN   Antifreeze                   2
GEN   Connection                   1
60A   Relief valve                 1
60A   High Pressure flow           1

There's a lot more values but you get the idea. I want to get the 5 most used parts per unit. The problem is, when i use the Select top 5 code it keeps giving me more records than I want where the 5th one has the same number as the next few records. Example: It works when the top 5 are 6 5 4 3 2 and the next highest is 1 but doesn't work when the top 5 are 5 4 3 2 1 and the next highest is 1. I have it organized descending by group so I really don't need it to look at the numbers. I just need it to pick the top 5 rows for each group and I can't get it to do that. I'm desperate!

SELECT tblIncidentByPartStep4.UnitType,
       tblIncidentByPartStep4.PartDescription,
       tblIncidentByPartStep4.New,
       tblIncidentByPartStep4.Count
 
FROM tblIncidentByPartStep4
 
WHERE (((tblIncidentByPartStep4.Count) In (
         SELECT top 5 Count
         FROM tblIncidentByPartStep4 AS Dupe
         WHERE dupe.Unittype = tblincidentByPartStep4.UnitType
         ORDER BY dupe.count desc )));

[FishVal]

Hello, linzeyd.

The problem is that the table has no primary key or any other unique field.
Add one and change WHERE clause in main query to check whether the unique field is within TOP 5 subset returned by the subquery.

Regards,
Fish

[FishVal]

Actually, after a preliminary testing, appears that even with PK field Access db engine shows definite flaws in certain combinations of TOP predicate and ordering by a field having duplicates.
I think it relates to query execution order internal optimizations, because TOP predicate works well with already ordered subquery:

SELECT TOP ..  * FROM (SELECT * FROM <table> WHERE ... ORDER BY ...);
 

Having played a little with it I would say that SQL/VBA hybride would be preferrable.

Names in example below are slightly different but I guess quite recognizible.
[keyID] is PK field - Autonumber(Long ), but could be any unique field of any type.

Code module:

Public Function IsInTop(varPK As Variant, varUnitType As Variant, lngTopCount As Long) As Boolean
 
    Dim rs As New ADODB.Recordset
 
    If IsNull(varPK) Or IsNull(varUnitType) Then Exit Function
 
    With rs
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open "SELECT keyID FROM t WHERE txtUnitType='" & varUnitType & _
            "' ORDER BY lngCount DESC;"
        For i = 1 To lngTopCount
            If !keyID = varPK Then IsInTop = True: GoTo ExitPoint
            .MoveNext
        Next i
ExitPoint:
        .Close
    End With
 
    Set rs = Nothing
 
End Function
 

Query:

SELECT t.*
FROM t
WHERE IsInTop(t.keyID, t.txtUnitType,5);
 

[NeoPa]

Actually, this behaviour is by design. The subquery, in which the TOP predicate is used, will return all records where the TOP 5 found values match. It does not guarantee to return only five records.

Furthermore, even had it returned only 5 records (a level of random guessing would be needed to determine which to select as the SQL doesn't specify), the values would be the same list of values anyway. This means that your SQL would STILL show however many records match those top 5 values, regardless of how many are returned in the subquery.

To avoid this behaviour (not very straightforward I'm afraid), add another field (preferably something unique) to the ORDER BY clause and then include this also within the WHERE clause of the main SQL.

There's more than one way to do this, but I'll include an example to illustrate :

SELECT [UnitType],
       [PartDescription],
       [New],
       [Count]
 
FROM tblIncidentByPartStep4
 
WHERE [Count] & [PartDescription] In(
         SELECT TOP 5 [Count] & [PartDescription]
         FROM tblIncidentByPartStep4 AS Dupe
         WHERE Dupe.UnitType=tblincidentByPartStep4.UnitType
         ORDER BY [Count] DESC,
                  [PartDescription])

[FishVal]

Actually, after a preliminary testing, appears that even with PK field Access db engine shows definite flaws in certain combinations of TOP predicate and ordering by a field having duplicates.
I think it relates to query execution order internal optimizations, because TOP predicate works well with already ordered subquery:

SELECT TOP ..  * FROM (SELECT * FROM <table> WHERE ... ORDER BY ...);
 

;)

Actually that is well-known and even specified behavior.
It is not a bad idea to RTFM sometimes.

TOP n [PERCENT]
Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;
 

If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.

The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

Regards,
Fish

[NeoPa]

;)

Actually that is well-known and even specified behavior.
It is not a bad idea to RTFM sometimes.

Regards,
Fish

Just to make this clear - Fish was talking to himself when he said this.

@Linzey, this was not directed at you at all.

@Fish, we can't know all things all the time. We get rusty on things sometimes. I know I did (and do).

[FishVal]

Just to make this clear - Fish was talking to himself when he said this.

Just to make this absolutely clear - fishes don't talk at all. :P
Especially with human beings.