Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts

    Question Incorrect data when getting max() function

    Hello

    Pls consider the following DDL

    Code:
    CREATE TABLE IF NOT EXISTS `employee` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(64) DEFAULT NULL,
      `salary` int(10) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
    
    --
    -- Dumping data for table `employee`
    --
    
    INSERT INTO `employee` (`id`, `name`, `salary`) VALUES
    (1, 'Matt', 1000),
    (2, 'Joe', 2000),
    (3, '', 3000),
    (4, 'Peter', 4000),
    (5, '', 6000),
    (6, '(null)', 3500),
    (7, 'Jason', 4500),
    (8, 'James', 0);

    With the above data, when I am running the following query:

    Code:
    SELECT name, max( salary ) AS sal
    FROM `employee`
    WHERE salary < (
    SELECT max( salary )
    FROM employee )
    I am getting the result as :

    Code:
    name | sal 
    Matt 	| 4500
    Instead of:
    Code:
    name | sal 
    Jason	| 4500
    Can someone please tell me whats wrong in my query?

    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    Code:
    SELECT name, salary
    FROM employee
    WHERE salary < ( SELECT max( salary ) FROM employee )
    ORDER BY salary DESC LIMIT 1
    Another way to do this:
    Code:
    SELECT name, salary
    FROM ( 
        SELECT name, salary FROM employee
        ORDER BY salary DESC LIMIT 2 ) AS X
    ORDER BY salary ASC LIMIT 1
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (09-25-2013)

  • #3
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts
    Hi

    Thanks for the reply but I want to do this without using LIMIT


    Thanks

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    Quote Originally Posted by cancer10 View Post
    Thanks for the reply but I want to do this without using LIMIT
    Why? Because you want to be less efficient?

    Code:
    SELECT name, salary FROM employee AS E
    INNER JOIN
         ( SELECT MAX(salary) AS nextToMaxSalary FROM employee
           WHERE salary < 
               ( SELECT MAX(salary) FROM employee ) 
         ) AS X
    ON E.salary = X.nextToMaxSalary
    But fair warning: If you have two people with the same next-to-max salary this query will get both of them. Or if you have two people with the same max salary, this query will skip both of them.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (09-26-2013)

  • #5
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts
    Thanks, works like a charm...


  •  

    Posting Permissions

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