SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Little help w/ query?

    I have this:
    PHP Code:
    SELECT
    manufacturer
    .name AS manufacturer,
    model.name AS model,
    status.name AS status,
    tire.manufacturer_id,
    tire.width,
    tire.sidewall_height,
    tire.rim_diameter,
    tire.unit_price,
    tire.unit_special,
    tire.model_id
    FROM tire
    LEFT JOIN manufacturer ON manufacturer
    .manufacturer_id 1tire.manufacturer_id
    LEFT JOIN model ON model
    .model_id tire.model_id
    LEFT JOIN status ON status
    .status_id tire.status_id
    WHERE 
    (width 205 AND sidewall_height 55 AND rim_diameter 17 AND tire.status_id 2)
    OR (
    width 215 AND sidewall_height 45 AND rim_diameter 17 18> AND tire.status_id 2)
    OR (
    width 215 AND sidewall_height 40 AND rim_diameter 17 AND tire.status_id 2)
    OR (
    width 195 AND sidewall_height 60 AND rim_diameter 17 AND tire.status_id 2)
    GROUP BY manufacturer_id ORDER BY IF(unit_special>0,unit_special,unit_price
    The thing is I need it to group by manufacturer_id as you see in line 21 but selecting the one with the lowest unit_special. Unless unit_special equals 0, in which case it needs to take into account the unit_price.

    I'm also doing this ordering at the end so I get the results sorted the way I need but since the query groups BEFORE ordering, I don't get the actual lowest price.

    In general I need to get back all the manufacturer_id ordered by price ascending. The catch is I only need ONE instance per manufacturer_id and this instance should reflect the lowest price possible (be it unit_price or unit_special).

    What do you think?

    Can it be done in one query?

    Thnak you,
    Last edited by lgomez; Jun 16, 2003 at 06:03.
    Luis

  2. #2
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ** BUMP **
    PHP Code:
    SELECT
    manufacturer
    .name AS manufacturer,
    model.name AS model,
    status.name AS status,
    tire.manufacturer_id,
    tire.width,
    tire.sidewall_height,
    tire.rim_diameter,
    tire.unit_price,
    tire.unit_special,
    tire.model_idFROM tireLEFT JOIN manufacturer ON manufacturer.manufacturer_id tire.manufacturer_id
    LEFT JOIN model ON model
    .model_id tire.model_id
    LEFT JOIN status ON status
    .status_id tire.status_idWHERE (width 205 AND sidewall_height 55 AND rim_diameter 17 AND tire.status_id 2)
    OR (
    width 215 AND sidewall_height 45 AND rim_diameter 17 AND tire.status_id 2)
    OR (
    width 215 AND sidewall_height 40 AND rim_diameter 17 AND tire.status_id 2)
    OR (
    width 195 AND sidewall_height 60 AND rim_diameter 17 AND tire.status_id 2)GROUP BY manufacturer_id ORDER BY IF(unit_special>0,unit_special,unit_price
    Next time can you post your queries like this ? Without line numbers... Thanks.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    luis, i'm going to guess that you're on mysql, and unless you're on the latest version which does subqueries, you're not going to be able to do what i think you want (it's not really all that clear) in one query

    you said "selecting the one with the lowest unit_special"

    see 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field
    http://www.mysql.com/doc/en/example-...group-row.html

    rudy
    http://r937.com/

  4. #4
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I'm late...

    Thanks a lot. This really helped.

    PHP Code:
    ORDER BY IF(unit_special>0,unit_special,unit_price
    Luis


Bookmarks

Posting Permissions

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