mysql query incorrect output

Question

I've a mysql table of simple data like:

alt text

now when i execute this simple query:

SELECT MAX(cashback) as MAX, MIN(cashback) as MIN FROM pearlcashback_retailers_category WHERE retailer_id='32' AND cashback NOT LIKE '%\%'

it outputs:

alt text

and for this query:

SELECT MAX(cashback) as MAX, MIN(cashback) as MIN FROM pearlcashback_retailers_category WHERE retailer_id='32' AND cashback LIKE '%\%'

it outputs:

alt text

please, help me...

sql of the table & data:

CREATE TABLE IF NOT EXISTS `pearlcashback_retailers_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `retailer_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `cashback` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;


INSERT INTO `pearlcashback_retailers_category` (`id`, `retailer_id`, `category_id`, `cashback`) VALUES
(1, 32, 17, '46%'),
(2, 32, 14, '40'),
(11, 4, 15, '27%'),
(9, 32, 15, '5'),
(8, 32, 7, '44%'),
(14, 3, 13, '1');

You have two different queries and they are working fine....what is the question? what help do you need? — Johnbabu Koppolu
– Johnbabu Koppolu, Commented Dec 19, 2010 at 11:09

bobflux · Accepted Answer · 2010-12-19 11:15:36Z

2

Your cashback column is VARCHAR which is a text type, therefore min(), max() etc all operate on alphabetical sorting (which is normal), therefore "b" > "aaaa", and of course "5" > "100".

Use the correct types.

answered Dec 19, 2010 at 11:15

bobflux

11.6k3 gold badges31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Johnbabu Koppolu · Accepted Answer · 2010-12-19 11:43:31Z

1

You are sorting a VARCHAR column - cashback, string '5' is greater than string '40', in other words if you arrange '5' and '40' as in a dictionary, '5' comes after '40', hence ther result MAX - '5' and MIN '40',

In the other case - if you arrange '46%' and '44%' as in a dictionary, '46%' comes later than '44%', hence MAX '46%' and MIN '44%'

edited Dec 19, 2010 at 11:43

answered Dec 19, 2010 at 11:32

Johnbabu Koppolu

3,2622 gold badges26 silver badges34 bronze badges

Comments

Alex Pliutau · Accepted Answer · 2010-12-19 11:10:11Z

0

Your cashback field is incorrect. Data should be in one format. If you store in this field only percentages symbol % is not required. It will be good for your query and your architecture. Sorry for my english.

answered Dec 19, 2010 at 11:10

Alex Pliutau

22k28 gold badges115 silver badges144 bronze badges

2 Comments

Johnbabu Koppolu Over a year ago

Is that an answer or a comment?

Vaibhav Gupta Over a year ago

yes... i know but as i am first electing only the numeric fields not with % fields.. so, why the output is wrong??

bernd_k · Accepted Answer · 2010-12-19 11:16:33Z

0

cashback seem to be a varchar column and for such '40' < '5' is true. I'm not sure if the following works with mysql:

SELECT MAX(cashback) as MAX, MIN(cashback) as MIN 
FROM pearlcashback_retailers_category 
WHERE retailer_id='32' AND cashback NOT LIKE '%\%'
ORDER BY cast(cashback as integer)

answered Dec 19, 2010 at 11:16

bernd_k

12.1k7 gold badges49 silver badges64 bronze badges

Collectives™ on Stack Overflow

mysql query incorrect output

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related