Skip to content
Advertisement

Query using min() not showing the correct result

I have this table:

CREATE TABLE IF NOT EXISTS `Vars` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(15) NOT NULL,
  `userID` bigint(20) NOT NULL,
  `viewedT` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

I have these values:

INSERT INTO `Vars` (`ID`, `code`, `userID`, `viewedT`) VALUES
(1, 'alex1234567', 1, 25),
(2, 'Dan1234567', 2, 15);

Now the weird thing is that I can not get the correct results with this query:

select min(`viewedT`), `code` From Vars

The result is:

 min(`viewedT`)     Code
 15                 alex1234567

EDIT: What I need is to select the code which have min(viewsT)

I’m sure that I’m missing something really small in the syntax which will bring me downvotes, but I need to know my mistake and learn from it.

Advertisement

Answer

Assuming you want to find the one row with the minimum value, use

select viewedT, code, id from vars
  where viewedT= (select min(viewedT) from vars)

If more than one row contains the minimum value you will get all such rows. I.e. if two rows had the value 15 for viewedT, you would get both.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement