Search code examples
mysqlsqlmaxmin

MySQL max and MIN function showing 100 and above as minimum value

I made a query to show maximum and lowest value from database but it doesn't work perfectly. In cases where we have values from 100 and above it returns it lowest instead of 99 and below

SELECT MIN(total) AS min, MAX(total) AS max  FROM `results` WHERE `term_id` = 2 AND  `class_id` = 8 AND `subject_id` = 152

Result

min     max     
100     96

enter image description here

This is my table structure:

CREATE TABLE `results` (
  `rst_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `first_test` varchar(2) DEFAULT NULL,
  `second_test` varchar(2) DEFAULT NULL,
  `exam` varchar(2) DEFAULT NULL,
  `total` varchar(3) NOT NULL,
  `position` int(11) DEFAULT NULL,
  `term_id` int(11) NOT NULL,
  `session_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `is_new` tinyint(1) DEFAULT '1',
  `is_upd` tinyint(1) NOT NULL DEFAULT '0',
  `date_declared` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `declared_by` int(11) NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Solution

  • As i thought you have a varchar and that is sorted by lexicographical order

    use

    SELECT MIN(total + 0) AS min, MAX(total + 0) AS max  
FROM `results` 
WHERE `term_id` = 2 AND  `class_id` = 8 AND `subject_id` = 152

    this will convert it into a number