Need Top 3 within 2 categories?

ions · Mar 27, 2009

Dear MsAccess Query expert.

Suppose I have the following DataSet.

Type, Date, Price
A, 02/21/09, 1.00
A, 03/21/09, 1.15
B, 02/21/09 1.23
A, 12/21/08 1.00
A, 11/21/08 3.00
B, 03/21/09 4.00
B, 01/21/09 5.00
A, 01/21/09 6.00
B, 07/21/08 2.00

I want a new recordset that gives me the Top 3 most recent A records, and the TOP 3 most Recent B records, for a total of always only 6 records.

Can I do this in Access using the Query grid or do I have to write the SQL?

How would I write the SQL?

Thank you

Peter.

gemma-the-husky · Mar 27, 2009

always just A's and B's records?

have one query to find 3 top As
have another query to find 3 top Bs
union them together

raskew · Mar 27, 2009

Hi -

You'd need to use a subquery for this. Here's an example using
Northwind's Categories and Products tables. It returns the top
three products per category, based on UnitsInStock.

Code:

SELECT Categories.CategoryName AS Expr1, Products.ProductName, Products.UnitsInStock
FROM Categories, Products
WHERE (((Products.UnitsInStock) [COLOR="Blue"]In (SELECT
    Top 3 [UnitsInStock] 
FROM
   Products 
WHERE
   [CategoryID]=[Categories].[CategoryID] 
ORDER BY
   [UnitsInStock] Desc)))[/COLOR]
ORDER BY Categories.CategoryName, Products.UnitsInStock DESC;

HTH - Bob

Added:

Allen Browne provides excellent guidance re subqueries here: http://allenbrowne.com/subquery-01.html

ions · Mar 27, 2009

Thank you Both

ions · Mar 27, 2009

Thank you for your input

I understand that I must create two TOP 3 queries (One for category A and another for category B) and then Union them together to get my final recordset.

Suppose I wanted to take this one step further. Suppose I introduce a new Column named Parent.

Parent,Type, Date, Price
1,A, 02/21/09, 1.00
1,A, 03/21/09, 1.15
1,B, 02/21/09 1.23
1,A, 12/21/08 1.00
1,A, 11/21/08 3.00
1,B, 03/21/09 4.00
1,B, 01/21/09 5.00
1,A, 01/21/09 6.00
1,B, 07/21/08 2.00
2,A, 02/21/09, 1.00
2,A, 03/21/09, 1.15
2,B, 02/21/09 1.23
2,A, 12/21/08 1.00
2,A, 11/21/08 3.00
2,B, 03/21/09 4.00
2,B, 01/21/09 5.00
2,A, 01/21/09 6.00
2,B, 07/21/08 2.00
3,A, 02/21/09, 1.00
3,A, 03/21/09, 1.15
3,B, 02/21/09 1.23
3,A, 12/21/08 1.00
3,A, 11/21/08 3.00
3,B, 03/21/09 4.00
3,B, 01/21/09 5.00
3,A, 01/21/09 6.00
3,B, 07/21/08 2.00

Now I want to unionize (Parent1 Top 3 A + Parent1 Top 3 B) + (Parent2 Top 3 A + Parent2 Top 3 B) + (Parent3 Top 3 A + Parent3 Top 3 B) .... + (ParentN Top 3 A + ParentN Top 3 B)

This seems extremely complex. If I have N parents I would need to create ((A TOP 3 + B TOP 3) Union) * N Union queries.

If I have 30 parents that is 60 queries with two levels of union nesting

There must be a better way? Maybe an intermediate table?

Thanks.

gemma-the-husky · Mar 27, 2009

i said at the beginning - is there only a A and B level - because if there is more, it will become very complex

and i cant think of an easy way to do it - unless you can just select top3 in a query with a group by choice - but i dont think you can

Brianwarnock · Mar 27, 2009

Did a development of the approach shown by Bob, aka Raskew, not work?

Brian

Thinh · Mar 27, 2009

i not sure of what you are trying to do. show me the result and i might be able to figure it out. From the look i would suggesting looking into dictionary object.

ions · Mar 27, 2009

I might use VBA and SQL to construct my own temporary table.

I will construct the first level union using SQL and variables and then loop through each parent.

Can anyone think of a different method?

Husky, yes the original question was for the smaller recordset. I wanted to see the answer for the base case.

Thanks

ions · Mar 27, 2009

>>Did a development of the approach shown by Bob, aka Raskew, not work?

I believe Raskew wrote the SQL for TOP 3 for category A. We need the same SQL for Category B and then union the two. I believe Raskew had the same concept as Husky.

Two top 3 queries unionized.

Brianwarnock · Mar 27, 2009

I've not used it and have no data to experiment with but he specifically said per category.

Another approach might be cascading queries.
q1 max date per parent and type
q2 q1 linked to table on parent and type and max date where date<maxdate q1
q3 as per q2 but date<maxdate q2
q4 select records with date > maxdate q3

Brian

raskew · Mar 28, 2009

That sample query I provided returns the top 3 records for each of the 8 categories -- 24 records total. And, there's no limit on the number of categories. If there were 100, it'd return 300 records.

Suggest you copy it to Northwind and give it a try.

Bob

raskew · Mar 28, 2009

To provide a more relevant example, I created
tblTest5 and populated it with just a few of
the lines from your posting:

PHP:

MyType  MyDate  Price
A   2/21/2009   $1.00
A   3/21/2009   $1.15
B   1/21/2009   $6.00
B   7/21/2008   $1.23
B   2/21/2009   $5.00
A   11/21/2008  $3.00

...and then created the following
to return the Top 2 records of each
MyType (A & B), based on price.

Code:

SELECT
    tblTest5.MyType
  , tblTest5.MyDate
  , tblTest5.Price
FROM
   tblTest5
WHERE
   (((tblTest5.Price) In (
SELECT
    TOP 2 Price                               
FROM
   tblTest5 AS Dupe                                 
WHERE
   Dupe.MyType = tblTest5.MyType           
ORDER BY
   Dupe.MyType
  , Dupe.Price DESC)))
ORDER BY
   tblTest5.MyType
  , tblTest5.Price DESC;

...which returns:

PHP:

MyType	MyDate	Price
A	11/21/2008	$3.00
A	3/21/2009	$1.15
B	1/21/2009	$6.00
B	2/21/2009	$5.00

You should be able to adapt this query, modifying
table/field names as necessary.

Note that you are not limited to the number of Types
[MyType] and/or Top N (I used 2 to avoid having to
hand-feed a loft of data to the sample table).

Also note that both Date and Type are reserved words
in Access, which is why I modified the field names.

HTH - Bob

ions · Mar 28, 2009

Bob I will study this further tomorrow. It looks very interesting.

Thank you

Peter.

Brianwarnock · Mar 28, 2009

It pays to read Bob's posts .

Brian

raskew · Mar 29, 2009

To incorporate the Parent field, I created this query (Query9):

Code:

SELECT [Parent] & [MyType] AS Expr1, tbltest6.MyDate, tbltest6.Price
FROM tbltest6;

And then modified the previously described query to:

Code:

SELECT Query9.Expr1, Query9.MyDate, Query9.Price
FROM Query9
WHERE (((Query9.Price) In (SELECT
    TOP 3 Price                               
FROM
   Query9 AS Dupe                                 
WHERE
   Dupe.Expr1 = Query9.Expr1           
ORDER BY
   Dupe.Expr1
  , Dupe.Price DESC)))
ORDER BY Query9.Expr1, Query9.Price DESC;

This could be accomplished without creating Query9, but the SQL gets really messy.

Bob

ByteMyzer · Mar 29, 2009

Try the following SQL (substitute highlighted text with actual table/field names):

Code:

SELECT T1.*
FROM [b][i]MyTable[/i][/b] T1
WHERE T1.[b][i]Date[/i][/b] & T1.[b][i]Price[/i][/b] IN
(SELECT TOP 3 T2.[b][i]Date[/i][/b] & T2.[b][i]Price[/i][/b]
 FROM [b][i]MyTable[/i][/b] T2
 WHERE T2.[b][i]Parent[/i][/b] = T1.[b][i]Parent[/i][/b]
 AND   T2.[b][i]Type[/i][/b] = T1.[b][i]Type[/i][/b]
 ORDER BY T2.[b][i]Date[/i][/b] DESC)
ORDER BY T1.[b][i]Parent[/i][/b], T1.[b][i]Type[/i][/b], T1.[b][i]Date[/i][/b]

raskew · Mar 29, 2009

Hi -

That works very well! Solved the sql issues I was having problems with.

In testing it, I modified the Order By clauses

Code:

ORDER BY T2.[COLOR="Red"]Date[/COLOR] DESC)
ORDER BY T1.Parent, T1.Type, T1.[COLOR="red"]Date[/COLOR]

to:

Code:

 ORDER BY T2.[COLOR="red"]Price[/COLOR] DESC)))
ORDER BY T1.Parent, T1.MyType, T1.[COLOR="red"]Price[/COLOR] desc;

...in order to display the Top 3 per Parent/Type by price.

Best wishes - Bob

ions · Apr 1, 2009

Will give this a Try... Thank you

ions · Apr 1, 2009

I am getting confused if this example is for TOP 3 date or TOP 3 Price?

What I am looking for is the top 3 most recent (Date) Parent & Type combinations.

SELECT TOP 3 T2.Date & T2.Price 'Why are you concatenating this?

I put comments next to things I don't understand.

Code:

SELECT T1.*
FROM MyTable T1 ' ***What is MyTable? Just T1 no?
WHERE T1.Date & T1.Price IN   '***I am not interested in Top 3 price only Date.
(SELECT TOP 3 T2.Date & T2.Price
 FROM MyTable T2
 WHERE T2.Parent = T1.Parent  '** This part I understand
 AND   T2.Type = T1.Type  '** This part I understand
 ORDER BY T2.Date DESC)  '**Yes I want Desc Date of the Parent & Type combo
ORDER BY T1.Parent, T1.Type, T1.Date '**Yes this is exactly the order I want followed by the corresponding Price.

Thank you.

Need Top 3 within 2 categories?

Access User

Super Moderator

AWF VIP

Access User

Access User

Super Moderator

Retired

Registered User.

Access User

Access User

Retired

AWF VIP

AWF VIP

Access User

Retired

AWF VIP

AWF VIP

AWF VIP

Access User

Access User

Similar threads

Users who are viewing this thread