Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Angry Unanswered: SELECT TOP 1 from each category

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select id, description, price, onhand, category
    from products AA
    where price =
    ( select min(price)
    from products
    where category = AA.category )

    rudy
    http://r937.com/

  3. #3
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Question sorry, didnt get that

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Talking GOT IT!

    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  7. #7
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Thumbs up all right

    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
    Last edited by jonah9; 12-19-03 at 01:38.

  8. #8
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Question newest products?

    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?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ...since its matching records to that "1" price, it can still return more than one record with that price
    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!
    Last edited by r937; 12-19-03 at 08:20.

  10. #10
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Lightbulb its all good

    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
    Last edited by jonah9; 12-24-03 at 00:22.

  11. #11
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Angry BROKE AGAIN

    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

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    may we see the latest version of your query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    FINALLY

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •