I've tried everything i can think of:

how can i select the cheapest product from each of my 4 product categories?

any ideas?

thanks in advance,
Jonah

select id, description, price, onhand, category
from products AA
where price =
( select min(price)
from products
where category = AA.category )

rudy
http://r937.com/

thanks for the suggestion,
but could you give me a little explanation of what you did there?
im a little new to this and i couldnt follow you.
thanks a bunch,
Jonah

select the product with the price that is equal to the lowest price for all products in the same category



it's called a correlated subquery because the rows in the subquery are correlated to match the row of the main query via the correlation variable AA

it is essentially another way of grouping the rows by category and choosing the min price for each group, then using that to decide which row in each group to select

make more sense now?


rudy

wow, thats great,
i had no idea you could use variables in the select statement like that.

well, ya learn soemething new everyday huh.

oh, one more thing, when i ran the query, it returned more than one product in each category because the lowest prices for some of the products were the same. is there any way of getting it to display only one product? (i have no product ID field)

i tried

select DISTINCT price, id, description, onhand, category
from products AA
where price =
( select min(price)
from products
where category = AA.category )

but it still gave me doubles on the prices.


Thanks again,
Jonah

DISTINCT applies to the entire result set row, i.e. distinct combinations of all columns in the SELECT list

if you have more than one product with the lowest price in the category, which product do you want to select?

if you don't have some rule -- like, the one with the highest onhand quantity, the one with the earliest "best before" date, or something -- then you can try this alternate approach, which i would not recommend in the general case, because it will be completely arbitrary which row you get

select id, description, price, onhand, category
from products AA
where price =
( select top 1 price
from products
where category = AA.category
order by price desc )

sometimes that's okay, but most of the time, if you have a "tie" it is important to know about it

for example, suppose you wanted to find the salesperson who had the most sales in a given category

if you use the TOP 1 method, you will surely p*ss off somebody if there's a tie

think carefully before you write your queries!!!


rudy

hmm, tried the "top 1 " thing, but since its matching records to that "1" price, it can still return more than one record with that price, so it didnt make a difference. ill have to try to find some other way, like you said.

thanks for taking the time to help me out and explain everything.
i really appreciate it.

Sincerely,
Jonah

just had a thought,

if i wanted to run that query again but this time show only the newest product when the recordset returns 2 records for one price, how would i do that?

DOH!! yes, of course

my bad -- i gotta stop writing sql late at night

okay, suppose you have a dateadded field

select id, description, price, dateadded, onhand, category
from products AA
where dateadded =
( select top 1 dateadded
from products
where category = AA.category
and price =
( select top 1 price
from products
where category = AA.category
order by price asc)
order by dateadded desc )

rudy

Edit: also gotta make sure i have a coffee before coding sql in the morning!

hey, thanks again for the help,
i should probably be drinking a lot more coffee myself

i tried what you suggested and got this error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] At most one record can be returned by this subquery.

after playing around with it a little i got the error to go away but it was giving me all the products for each date, not just the cheapest price for each date. finally i got it working though, by doing the following:

SELECT DISTINCT price, category, dateadded FROM products AA WHERE dateadded = (SELECT TOP 1 dateadded FROM products WHERE category = AA.category) AND price = (SELECT min(price) FROM products WHERE category = AA.category)

basically just took the nested subquery out of the parentheses and broke it into two conditions for the main query.

thanks a lot, it was fun working on this with you,

Jonah

Well, it was working fine for awhile,
but we just updated our database and now this query doesnt work.

While i know there are 4 categories in the database, only 3 are now showing up on the web page, with the last one coming up blank.

i checked the database and everything seems fine. any ideas??
thanks,
Jonah

may we see the latest version of your query?

ok, sorry for the long delay, been out for awhile...

so, here is (hopefully) the final version of this query.
ill explain this in case it helps anybody else:

I was trying to display the cheapest flights for each region of the world. So i wanted to display only the city name and cheapest price from the database, but i kept coming up with doubles (because if the cheapest flight to Asia is to Bangkok for $500 in January on United Airlines, it might also be $500 in February on Korean Airlines, hence Bangkok would show up twice with the same price). Finally I wrote the select statement to pull the city with the cheapest price for each region, AND the month with the cheapest price for each region, AND the cheapest price for each region. LIke this:

SELECT price, regionID, city FROM airprices AA WHERE city = (SELECT TOP 1 city FROM airprices WHERE regionID = AA.regionID AND price = (SELECT min(price) FROM airprices WHERE regionID = AA.regionID)) AND month = (SELECT TOP 1 month FROM airprices WHERE regionID = AA.regionID AND price = (SELECT min(price) FROM airprices WHERE regionID = AA.regionID)) AND price = (SELECT min(price) FROM airprices WHERE regionID = AA.regionID) ORDER BY price ASC

it seems to be working so far. Ill keep you posted if anything goes wrong.

Thanks for all the help on this,
Jonah