Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2006
    Posts
    12

    Unanswered: max() and min() issue within join query

    I'm trying to join multiple tables and get the max and min values of the returned rows.

    Here is the query.

    Code:
    select
      ProductCodes.product_id
      ,ProductCodes.region
      ,ProductCodes.countrycode
      ,ProductCodes.fullcode
      ,ProductNames.product_name
      ,LCR.quality
      ,LCR.min_rate
      ,max(LCR.quality) as quality
      ,min(LCR.min_rate) as minRate
    from
      ProductCodes
      ,ProductNames
        left outer join LCR on ProductCodes.product_id = LCR.product_id
    where
      ProductCodes.product_id = ProductNames.product_id
      and ProductCodes.fullcode not in ('0','9370','9379')
      and ProductCodes.region=1
      and ProductCodes.countrycode=1684
      and LCR.min_rate > 0
    group by
       ProductNames.product_name;
    order by
       LCR.quality
       ,ProductNames.product_name
    Here is a quick mockup of the data

    LCR table,
    +-----------+---------+---------+
    | product_id | min_rate | quality |
    +-----------+---------+---------+
    | 455 | 0.00900 | 0 |
    | 456 | 0.06450 | 2 |
    | 457 | 0.05950 | 2 |
    | 458 | 0.01210 | 0 |
    | 459 | 0.01200 | 0 |
    +------------+----------+---------+


    ProductNames table,
    +------------+-------------------------------+
    | product_id | product_name |
    +------------+-------------------------------+
    | 455 | Tires |
    | 456 | Wheels |
    | 456 | Jacks |
    | 458 | Hammer |
    | 459 | Chocks |
    +------------+-------------------------------+

    ProductCodes,
    +------------+--------+-------------+----------+
    | product_id | region | countrycode | fullcode |
    +------------+--------+-------------+----------+
    | 456 | 1 | 93 | 93 |
    | 458 | 1 | 93 | 9379 |
    | 455 | 1 | 93 | 9370 |
    | 459 | 1 | 93 | 9320 |
    | 458 | 1 | 355 | 3554 |
    +------------+--------+-------------+----------+

    Now the problem that i'm having is that when i run the query i get the rate 0.00900 instead of the rate 0.05950 which is what i should be getting, or at least thought i should be getting. What i want to know is how do you set a'priority' thing when running both min and max, i want to be able to take the max quality first then out of that take the min rate. but it takes the max quality and the min rate regardless of f the quality. so i'm getting the quality 2 with the min rate of the quality 0.

    Any direction on this would be awesome.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select ProductCodes.product_id
         , ProductCodes.region
         , ProductCodes.countrycode
         , ProductCodes.fullcode
         , ProductNames.product_name
         , max(LCR.quality)       as maxquality
         , min(LCR.min_rate)      as minRate
      from ProductCodes
    inner
      join ProductNames
        on ProductNames.product_id = ProductCodes.product_id
    left outer 
      join LCR 
        on LCR.product_id = ProductCodes.product_id
       and LCR.min_rate > 0
     where ProductCodes.fullcode not in ('0','9370','9379')
       and ProductCodes.region=1
       and ProductCodes.countrycode=1684
    group 
        by ProductCodes.product_id
    order 
        by maxquality
         , ProductNames.product_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    12
    Thanks for the reply Rudy. But that gets me the same results as above. It's returning the lowest value with the highest quality. Not the lowest rate for the highest quality.

    I'm using version 4.0.24 without the ability to update the mysql version. I know i could do this is a view or stored proc.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could do it with a view? please show

    actually, "lowest rate for the highest quality" makes sense

    unfortunately the easiest way to do it is with a subquery

    you may have to write a temp table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2006
    Posts
    12
    I can't use subqueries since it's version 4.0.24. I had to create a tmp table for an earlier query. I might be able to use that same tmp table but i'll have to take a look once i get back to the office in the morning.

    The view is as i like to look at it a tmp table of it's own nature or at least that's how i use them. You can create the view to store the information that you need so that when you query that view "table" then you would get the results that we are looking for here. But damn them for being so far behind, i've gotten quite spoiled with the newer features.

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    For anyone interested in doing a test for themselves I have written out the DDL :
    Code:
    CREATE TABLE LCR (
     product_id INT UNSIGNED NOT NULL,
     min_rate FLOAT,
     quality INT
    );
    
    CREATE TABLE ProductNames (
     product_id INT UNSIGNED NOT NULL,
     product_name VARCHAR(255)
    );
    
    CREATE TABLE ProductCodes (
     product_id INT UNSIGNED NOT NULL,
     region INT,
     countrycode INT,
     fullcode INT
    );
    
    INSERT INTO LCR VALUES(455,0.00900,0),
    (456,0.06450,2),(457,0.05950,2),
    (458,0.01210,0),(459,0.01200,0);
    
    INSERT INTO ProductNames VALUES(455,'Tires'),
    (456,'Wheels'),(456,'Jacks'),(458,'Hammer'),(459,'Chocks');
    
    INSERT INTO ProductCodes VALUES(456,1,93,93),
    (458,1,93,9379),(455,1,93,9320),(459,1,93,9320),(458,1,355,3554);
    I will post a SQL query shortly.

  7. #7
    Join Date
    Oct 2006
    Posts
    12
    Ok well now i'm at a loss, i've been working on this thing all day and have create three memory tables that i'm using for tmp work and still don't get the results i'm looking for.

    I insert data into the first tmp table from 5 different tables. then i update another tmp table with a simple query. But the problem is that there are 6,000 entries into the tmp table so when i do my min and max it gives me the same results, the losest rate and the highest quality when i need the lowest rate for the highest quality.

    Any help on this would be awesome. thanks guys

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please explain why you can't upgrade to 4.1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2006
    Posts
    12
    In all honestly it's due to the lack of experience on the team to update a production server without it going down. We are supposed to be getting a new set of servers that we will install the latest version on but i don't know when that's going to be. it might take a year to get that done. so i can't wait that long on the issues i'm being brought today.

    I've takem my personal server and i couldn't even update that to the latest version so i don't want to jack up a production server that would bring the company to a halt lol

Posting Permissions

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