473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Select top N by group problem

I have a problem that's driving me crazy. i have a table that is a series of things like this
Expand|Select|Wrap|Line Numbers
  1. (Unit type) (part description)  (count)
  2. IWU   High pressure flowmeter      3
  3. IWU   Transformer                  2
  4. IWU   Fender                       2
  5. GEN   Fender                       2
  6. GEN   Antifreeze                   2
  7. GEN   Connection                   1
  8. 60A   Relief valve                 1
  9. 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!
Expand|Select|Wrap|Line Numbers
  1. SELECT tblIncidentByPartStep4.UnitType,
  2.        tblIncidentByPartStep4.PartDescription,
  3.        tblIncidentByPartStep4.New,
  4.        tblIncidentByPartStep4.Count
  5.  
  6. FROM tblIncidentByPartStep4
  7.  
  8. WHERE (((tblIncidentByPartStep4.Count) In (
  9.          SELECT top 5 Count
  10.          FROM tblIncidentByPartStep4 AS Dupe
  11.          WHERE dupe.Unittype = tblincidentByPartStep4.UnitType
  12.          ORDER BY dupe.count desc )));
Sep 13 '08 #1
6 15573
FishVal
2,653 Expert 2GB
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
Sep 13 '08 #2
FishVal
2,653 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP ..  * FROM (SELECT * FROM <table> WHERE ... ORDER BY ...);
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function IsInTop(varPK As Variant, varUnitType As Variant, lngTopCount As Long) As Boolean
  2.  
  3.     Dim rs As New ADODB.Recordset
  4.  
  5.     If IsNull(varPK) Or IsNull(varUnitType) Then Exit Function
  6.  
  7.     With rs
  8.         .ActiveConnection = CurrentProject.Connection
  9.         .CursorType = adOpenForwardOnly
  10.         .LockType = adLockReadOnly
  11.         .Open "SELECT keyID FROM t WHERE txtUnitType='" & varUnitType & _
  12.             "' ORDER BY lngCount DESC;"
  13.         For i = 1 To lngTopCount
  14.             If !keyID = varPK Then IsInTop = True: GoTo ExitPoint
  15.             .MoveNext
  16.         Next i
  17. ExitPoint:
  18.         .Close
  19.     End With
  20.  
  21.     Set rs = Nothing
  22.  
  23. End Function
  24.  
Query:
Expand|Select|Wrap|Line Numbers
  1. SELECT t.*
  2. FROM t
  3. WHERE IsInTop(t.keyID, t.txtUnitType,5);
  4.  
Sep 13 '08 #3
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [UnitType],
  2.        [PartDescription],
  3.        [New],
  4.        [Count]
  5.  
  6. FROM tblIncidentByPartStep4
  7.  
  8. WHERE [Count] & [PartDescription] In(
  9.          SELECT TOP 5 [Count] & [PartDescription]
  10.          FROM tblIncidentByPartStep4 AS Dupe
  11.          WHERE Dupe.UnitType=tblincidentByPartStep4.UnitType
  12.          ORDER BY [Count] DESC,
  13.                   [PartDescription])
Sep 14 '08 #4
FishVal
2,653 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP ..  * FROM (SELECT * FROM <table> WHERE ... ORDER BY ...);
  2.  
;)

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:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 25
  2. FirstName, LastName
  3. FROM Students
  4. WHERE GraduationYear = 1994
  5. ORDER BY GradePointAverage DESC;
  6.  
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
Sep 15 '08 #5
NeoPa
32,556 Expert Mod 16PB
;)

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).
Sep 15 '08 #6
FishVal
2,653 Expert 2GB
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.
Sep 15 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Irlan agous | last post by:
Hello i have t tables reactie and form and this query $sql = "select reactie.persid,form.oproep,form.foto,form.id from reactie,form INNER JOIN reactie ON (reactie.persid = form.id) group by...
10
by: Randell D. | last post by:
Folks, I have a SELECT that returns with multiple records - It works when I have a LIMIT clause but when I try to include a GROUP BY clause, the select returns nothing (ie no records, no...
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
2
by: areef.islam | last post by:
Hi, I am kinda new to javascript and I am having this problem with selecting multiple options from a select tag. Hope someone can help me out here. here is my code...
2
by: bhanab | last post by:
Hello, Please can someone help me with this select statement? How do I get the statement to keep the week number static? ie if there are no values then it should show up as NULL, zero or just stay...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
15
by: Simon Gare | last post by:
Hi, trying to retrieve postal codes from the db but only want the query to look at the first 3 digits of the code tried using (LEFT(dbo.booking_form.COLL_POST_CODE),3) but that doesn't work. I...
6
by: Jon Bilbao | last post by:
I´m trying a select clause in two steps because it´s too complex. First: SELECT Reference, Results.idEnsayo, Results.Num_taladro, min(Results.dTime) + 500 AS tIni, max(Results.dTime) - 500 AS...
1
by: zafm86 | last post by:
Hi everyone! I'm sure my problem is pretty easy to solve but I've been working on it for a long and my my brain is not working correctly anymore. I'm working with an AS400 and I mhave to do an...
4
by: The.Daryl.Lu | last post by:
Hi, Have a bit of a problem... I've created a form in Access and will use the form for a user to query a table based on the selected fields. The problem lies in that I was using checkboxes for...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.