SitePoint Sponsor

User Tag List

Results 1 to 19 of 19

Thread: Syntax MAX

  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Syntax MAX

    Hi all, hope in your help.

    I've this table in mysql:
    Code:
    UNITS	IND1	IND2
    AAA	84%	59%
    BBB	88%	56%
    I need this output with the max value in table:
    Code:
    UNITS	IND
    AAA	59%
    BBB	88%
    I tried this but the valur of units is incorrect:
    Code:
    mysql> SELECT UNITS,
    	MAX(IND1) AS ind1, MAX(IND2) as ind2
    FROM
    	`tbl_max`;
    +-------------------------+------+-----+
    | UNITS                   | ind1 | ind2|
    +-------------------------+------+-----+
    | AAA                     | 88%  | 59% |
    +-------------------------+------+-----+
    1 row in set
    Can you help me?
    thank you
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you forgot the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you forgot the GROUP BY clause
    thank you for suggestion but:
    Code:
    mysql> SELECT
    	units,
    	MAX(IND1) AS ind1,
    	MAX(IND2) AS ind2
    FROM
    	`tbl_max`
    GROUP BY units;
    +------------------------------+------+------+
    | units                        | ind1 | ind2 |
    +------------------------------+------+------+
    | BBB                          | 88%  | 56%  |
    | AAA                          | 84%  | 59%  |
    +------------------------------+------+------+
    4 rows in set
    I need this:
    Code:
    +------------------------------+------+
    | units                        | ind  |
    +------------------------------+------+
    | BBB                          | 88%  |
    | AAA                          | 59%  |
    +------------------------------+------+
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Two queries?

    Code:
    mysql> SELECT
    	units,
    	MAX(ind1)
    FROM
    	`tbl_max`
    GROUP BY
    	units
    ORDER BY
    	MAX(ind1) DESC
    LIMIT 1;
    
    
    
    SELECT
    	units,
    	MAX(ind2)
    FROM
    	`tbl_max`
    GROUP BY
    	units
    ORDER BY
    	MAX(ind2) DESC
    LIMIT 1;
    +-------+-----------+
    | units | MAX(ind1) |
    +-------+-----------+
    | BBB   | 88%       |
    +-------+-----------+
    1 row in set
    
    +-------+-----------+
    | units | MAX(ind2) |
    +-------+-----------+
    | AAA   | 59%       |
    +-------+-----------+
    1 row in set

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    Two queries?
    no

    also, LIMIT 1 is wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    I've this table in mysql:
    Code:
    UNITS	IND1	IND2
    AAA	84%	59%
    BBB	88%	56%
    this is a great example of what happens when your table design violates first normal form -- the result is, awkward, clumsy, and inefficient queries

    Code:
    SELECT units
         , MAX(ind) AS maxind
      FROM ( SELECT units
                  , ind1 AS ind
               FROM tbl_max
             UNION 
             SELECT units
                  , ind2 
               FROM tbl_max ) AS discombobulated
    GROUP 
        BY units;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no
    I was sure
    also, LIMIT 1 is wrong
    Why is wrong?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    Why is wrong?
    because you want more than one unit returned, you want all units returned
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is a great example of what happens when your table design violates first normal form -- the result is, awkward, clumsy, and inefficient queries

    Code:
    SELECT units
         , MAX(ind) AS maxind
      FROM ( SELECT units
                  , ind1 AS ind
               FROM tbl_max
             UNION 
             SELECT units
                  , ind2 
               FROM tbl_max ) AS discombobulated
    GROUP 
        BY units;
    hi Rudy, thank you for help but the output is always incorrect...
    Code:
    mysql> SELECT units
         , MAX(ind) AS maxind
      FROM ( SELECT units
                  , ind1 AS ind
               FROM tbl_max
             UNION 
             SELECT units
                  , ind2 
               FROM tbl_max ) AS discombobulated
    GROUP 
        BY units;
    +-------+--------+
    | units | maxind |
    +-------+--------+
    | AAA   | 84%    |
    | BBB   | 88%    |
    +-------+--------+
    2 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    ... but the output is always incorrect...
    please explain what's wrong with it

    also, please explain the correct output, not just with "expected output" that we have to figure out (because obviously, i figured it out wrong), but using words

    for example, in post #1, you want the "max" output, but AAA has two values, 84 and 59, and yet somehow you want 59, not 84 -- why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    okay...

    in this table
    Code:
    UNITS	IND1	IND2
    AAA	84%	59%
    BBB	88%	56%
    I need extract the max values of the ind1 and the ind2... for the ind1 the max values is 88% for ind2 the max value is 59%
    I've got several problems here because the data may not be normalised ...
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    I've got several problems here because the data may not be normalised ...
    yes, you do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    really can you? how?
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    write a query to find the max ind1 value

    then use that query as a subquery in the FROM clause of a new query, and join it back to the table

    this allows you to pull whatever unit has the max value

    repeat for the other ind
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    okay thank you ... I don't go beyond this query ... the value of units in ind 59% is incorrect... AAA instead of BBB ....
    Code:
    mysql> SELECT
    	units,
    	ind
    FROM
    	(
    		SELECT
    			units,
    			MAX(ind1) AS ind
    		FROM
    			tbl_max
    		UNION
    			SELECT
    				units,
    				MAX(ind2) AS ind
    			FROM
    				tbl_max
    	) AS p;
    +-------+-----+
    | units | ind |
    +-------+-----+
    | AAA   | 88% |
    | AAA   | 59% |
    +-------+-----+
    2 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    okay thank you ...
    i don't think you understood what i suggested...

    write a query to find the max ind1 value
    Code:
    SELECT MAX(ind1) AS max_ind1
      FROM tbl_max
    then use that query as a subquery in the FROM clause of a new query, and join it back to the table
    Code:
    SELECT t.units
         , t.ind1
      FROM ( SELECT MAX(ind1) AS max_ind1
             FROM tbl_max ) AS m
    INNER
      JOIN tbl_max AS t
        on t.ind1 = m.max_ind1
    repeat for the other ind
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    okay, your suggestion are two different queries?
    Code:
    mysql> SELECT
    	t.units,
    	t.ind1
    FROM
    	(
    		SELECT
    			MAX(ind1) AS max_ind1
    		FROM
    			tbl_max
    	) AS m
    INNER JOIN tbl_max AS t ON t.ind1 = m.max_ind1;
    
    SELECT
    	t.units,
    	t.ind2
    FROM
    	(
    		SELECT
    			MAX(ind2) AS max_ind2
    		FROM
    			tbl_max
    	) AS m
    INNER JOIN tbl_max AS t ON t.ind2 = m.max_ind2;
    +-------+------+
    | units | ind1 |
    +-------+------+
    | BBB   | 88%  |
    +-------+------+
    1 row in set
    
    +-------+------+
    | units | ind2 |
    +-------+------+
    | AAA   | 59%  |
    +-------+------+
    1 row in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    use your imagination

    how would you combine two identically structured result sets?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    use your imagination

    how would you combine two identically structured result sets?
    you are right... teacher thanks so much ...
    Code:
    mysql> SELECT
    	t.units,
    	t.ind1
    FROM
    	(
    		SELECT
    			MAX(ind1) AS max_ind1
    		FROM
    			tbl_max
    	) AS m
    INNER JOIN tbl_max AS t ON t.ind1 = m.max_ind1
    UNION ALL
    	SELECT
    		t.units,
    		t.ind2
    	FROM
    		(
    			SELECT
    				MAX(ind2) AS max_ind2
    			FROM
    				tbl_max
    		) AS m
    	INNER JOIN tbl_max AS t ON t.ind2 = m.max_ind2;
    +-------+------+
    | units | ind1 |
    +-------+------+
    | BBB   | 88%  |
    | AAA   | 59%  |
    +-------+------+
    2 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •