Top 2 entrees for each product 2

[bernie10]

Suppose I've got the following table:

Product Price
1 $3.32
1 $5.30
1 $6.73
2 $4.53
2 $3.21
2 $2.34

(so forth)

and I want a query to display only the top two values for each product(for a total of four entrees in this case). I know how to get the top two prices of the whole table but I don't know how to get the top two in each product. Any help would be appreciated.

Thanks,
CM

 

[PHV]

SELECT A.Product, A.Price
FROM yourTable A
WHERE A.Price IN (SELECT TOP 2 Price FROM yourTable B WHERE B.Product=A.Product ORDER BY Price DESC);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

PHV's response assumes "the top two values" means the maximum two values. If I were paying the price, I my top two choices would be the minimum two values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bernie10]

Yea I should have phrased the question better. But I just took PH's solution and sorted the price in ascending order instead and it gave me the minimum two values. Thanks for both of your responses.

 

[garlanj]

Sorry - I'm sure I've missed something really fundamental here but I don't get what the Table A and Table B are about - I thought there was only one table? The reason I ask is that I've got a very similar question at thread 1015660.

 

[SecondToNone]

It just one table but A and B are aliases of the same table...

-SecondToNone

 

[lespaul]

this query is using a correlated sub-query. The tables have an alias. The alias for the first instance of the table is A the alias for the second instance of the table is B. It's a correlated subquery when you want to find something in table B where it's the same key in table A.

taking this query:

SELECT A.Product, A.Price
FROM yourTable A
WHERE A.Price IN (SELECT TOP 2 Price FROM yourTable B WHERE B.Product=A.Product ORDER BY Price DESC);

the subquery (red) gets the two lowest prices for EACH product because it's correlated to the first query on the Product.



Leslie

 

[garlanj]

Thanks guys - it all becomes clear. Just one more thing (to take advantage of your good nature and expertise) - rather than selecting the top x, I want to select a random 3 from each product. Any suggestions on how that should be coded?