Simple mySQL query returning wrong results

Tapan · December 8, 2014, 1:05pm

Hi,

I have the following table:

[code]CREATE TABLE IF NOT EXISTS prices_content (
prcn_id mediumint(9) NOT NULL AUTO_INCREMENT,
prcn_pric_id mediumint(9) NOT NULL,
prcn_width int(11) NOT NULL,
prcn_drop int(11) NOT NULL,
prcn_price float(10,2) NOT NULL,
prcn_wholesale float(10,2) NOT NULL,
PRIMARY KEY (prcn_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=223 ;

–
– Dumping data for table `prices_content`

INSERT INTO prices_content (prcn_id, prcn_pric_id, prcn_width, prcn_drop, prcn_price, prcn_wholesale) VALUES
(1, 2, 3300, 1400, 42.00, 0.00),
(2, 2, 3300, 1100, 39.38, 0.00),
(3, 1, 2700, 1600, 39.90, 0.00),
(4, 2, 1600, 2100, 29.40, 0.00),
(5, 2, 3300, 900, 36.23, 0.00),
(6, 2, 1600, 1800, 28.35, 0.00),
(7, 1, 1600, 2300, 30.98, 0.00),
(8, 1, 1600, 2500, 32.55, 0.00),
(9, 1, 1600, 2700, 34.13, 0.00),
(10, 1, 1800, 900, 23.63, 0.00),
(11, 1, 1800, 1100, 25.20, 0.00),
(12, 1, 1800, 1400, 26.78, 0.00),
(13, 1, 1800, 1600, 29.40, 0.00),
(14, 1, 1800, 1800, 30.98, 0.00),
(15, 1, 1800, 2100, 32.55, 0.00),
(16, 1, 1800, 2300, 34.13, 0.00),
(17, 1, 1800, 2500, 35.70, 0.00),
(18, 1, 1800, 2700, 37.28, 0.00),
(19, 1, 2100, 900, 25.73, 0.00),
(20, 1, 2100, 1100, 27.83, 0.00),
(21, 1, 2100, 1400, 29.93, 0.00),
(22, 1, 2100, 1600, 31.50, 0.00),
(23, 1, 2100, 1800, 33.60, 0.00),
(24, 1, 2100, 2100, 35.18, 0.00),
(25, 1, 2100, 2300, 37.28, 0.00),
(26, 1, 2100, 2500, 39.38, 0.00),
(27, 1, 2100, 2700, 41.48, 0.00),
(28, 1, 2300, 900, 28.35, 0.00),
(29, 1, 2300, 1100, 30.45, 0.00),
(30, 1, 2300, 1400, 32.03, 0.00),
(31, 1, 2300, 1600, 34.13, 0.00),
(32, 2, 3300, 2700, 58.28, 0.00),
(33, 2, 3300, 2500, 55.65, 0.00),
(34, 2, 3300, 2300, 53.03, 0.00),
(35, 2, 3300, 2100, 50.40, 0.00),
(36, 2, 3300, 1800, 47.25, 0.00),
(37, 2, 3300, 1600, 44.63, 0.00),
(38, 2, 3300, 1800, 47.25, 0.00),
(39, 2, 3300, 1600, 44.63, 0.00),
(40, 2, 3000, 2700, 56.13, 0.00),
(41, 2, 3000, 2500, 52.50, 0.00),
(42, 2, 3000, 2300, 50.40, 0.00),
(43, 2, 3000, 2100, 47.25, 0.00),
(44, 2, 3000, 1800, 44.63, 0.00),
(45, 2, 3000, 1600, 42.00, 0.00),
(46, 2, 3000, 1400, 39.90, 0.00),
(47, 2, 3000, 1100, 36.75, 0.00),
(48, 2, 3000, 900, 34.13, 0.00),
(49, 2, 2700, 2700, 51.98, 0.00),
(50, 2, 2700, 2500, 48.83, 0.00),
(51, 2, 2700, 2300, 46.73, 0.00),
(52, 2, 2700, 2100, 44.10, 0.00),
(53, 2, 2700, 1800, 42.00, 0.00),
(54, 2, 1600, 1600, 26.25, 0.00),
(55, 2, 1600, 1400, 24.68, 0.00),
(56, 2, 1600, 1100, 23.10, 0.00),
(57, 2, 1600, 900, 21.53, 0.00),
(58, 2, 1400, 2700, 30.45, 0.00),
(59, 2, 1400, 2500, 29.40, 0.00),
(60, 2, 1400, 2300, 27.83, 0.00),
(61, 2, 1400, 2100, 26.25, 0.00),
(62, 2, 1400, 1800, 24.68, 0.00),
(63, 2, 1400, 1600, 23.63, 0.00),
(64, 2, 1400, 1400, 22.05, 0.00),
(65, 2, 1400, 1100, 21.00, 0.00),
(66, 2, 1400, 900, 19.43, 0.00),
(67, 2, 1100, 2700, 26.78, 0.00),
(68, 2, 1100, 2500, 25.73, 0.00),
(69, 2, 1100, 2100, 24.68, 0.00),
(70, 2, 1100, 2100, 23.63, 0.00),
(71, 2, 1100, 1800, 22.05, 0.00),
(72, 2, 1100, 1600, 21.00, 0.00),
(73, 2, 1100, 1400, 19.95, 0.00),
(74, 2, 1100, 1100, 18.90, 0.00),
(75, 2, 1100, 900, 17.85, 0.00),
(76, 2, 700, 2700, 23.62, 0.00),
(77, 2, 700, 2500, 19.30, 0.00),
(78, 2, 700, 2300, 18.37, 0.00),
(79, 2, 700, 2100, 17.85, 0.00),
(80, 2, 700, 1800, 16.80, 0.00),
(81, 2, 700, 1600, 15.75, 0.00),
(82, 2, 700, 1400, 14.70, 0.00),
(83, 2, 700, 1100, 14.17, 0.00),
(84, 2, 700, 900, 13.12, 0.00),
(85, 2, 400, 2700, 16.80, 0.00),
(86, 2, 400, 2500, 15.75, 0.00),
(87, 2, 400, 2300, 15.22, 0.00),
(88, 2, 400, 2100, 14.17, 0.00),
(89, 2, 400, 1800, 13.65, 0.00),
(90, 2, 400, 1600, 13.12, 0.00),
(91, 2, 400, 1400, 12.60, 0.00),
(92, 2, 400, 1100, 11.55, 0.00),
(93, 2, 400, 900, 11.02, 0.00),
(94, 2, 2700, 1400, 36.75, 0.00),
(95, 2, 2700, 1100, 34.65, 0.00),
(96, 2, 2700, 900, 32.55, 0.00),
(97, 2, 2500, 2700, 47.78, 0.00),
(98, 2, 2500, 2500, 45.68, 0.00),
(99, 2, 2500, 2300, 43.58, 0.00),
(100, 2, 2500, 2100, 41.48, 0.00),
(101, 2, 2500, 1800, 39.38, 0.00),
(102, 2, 2500, 1600, 36.75, 0.00),
(103, 2, 2500, 1400, 34.65, 0.00),
(104, 2, 2500, 1100, 32.55, 0.00),
(105, 2, 2500, 900, 30.45, 0.00),
(106, 2, 2300, 2700, 44.63, 0.00),
(107, 2, 2300, 2500, 42.53, 0.00),
(108, 2, 2300, 2300, 40.43, 0.00),
(109, 2, 2300, 2100, 38.85, 0.00),
(110, 2, 2300, 1800, 36.23, 0.00),
(111, 2, 2300, 1600, 34.13, 0.00),
(112, 2, 2300, 1400, 32.03, 0.00),
(113, 2, 2300, 1100, 30.45, 0.00),
(114, 2, 2300, 900, 28.35, 0.00),
(115, 2, 2100, 2700, 41.48, 0.00),
(116, 2, 2100, 2500, 39.38, 0.00),
(117, 2, 2100, 2300, 37.28, 0.00),
(118, 2, 2100, 2100, 35.18, 0.00),
(119, 2, 2100, 1800, 33.60, 0.00),
(120, 2, 2100, 1600, 31.50, 0.00),
(121, 2, 2100, 1400, 29.93, 0.00),
(122, 2, 2100, 1100, 27.83, 0.00),
(123, 2, 2100, 900, 25.73, 0.00),
(124, 2, 1800, 2700, 37.28, 0.00),
(125, 2, 1800, 2500, 35.70, 0.00),
(126, 2, 1800, 2300, 34.13, 0.00),
(127, 2, 1800, 2100, 32.55, 0.00),
(128, 2, 1800, 1800, 30.98, 0.00),
(129, 2, 1800, 1600, 29.40, 0.00),
(130, 2, 1800, 1400, 26.78, 0.00),
(131, 2, 1800, 1100, 25.20, 0.00),
(132, 2, 1800, 900, 23.63, 0.00),
(133, 2, 1600, 2700, 34.13, 0.00),
(134, 2, 1600, 2500, 32.55, 0.00),
(135, 2, 1600, 2300, 30.98, 0.00),
(136, 2, 2700, 1600, 39.90, 0.00),
(137, 1, 2300, 1800, 36.23, 0.00),
(138, 1, 2300, 2100, 38.85, 0.00),
(139, 1, 2300, 2300, 40.43, 0.00),
(140, 1, 2300, 2500, 42.53, 0.00),
(141, 1, 2300, 2700, 44.63, 0.00),
(142, 1, 2500, 900, 30.45, 0.00),
(143, 1, 2500, 1100, 32.55, 0.00),
(144, 1, 2500, 1400, 34.65, 0.00),
(145, 1, 2500, 1600, 36.75, 0.00),
(146, 1, 2500, 1800, 39.38, 0.00),
(147, 1, 2500, 2100, 41.48, 0.00),
(148, 1, 2500, 2300, 43.58, 0.00),
(149, 1, 2500, 2500, 45.68, 0.00),
(150, 1, 2500, 2700, 47.78, 0.00),
(151, 1, 2700, 900, 32.55, 0.00),
(152, 1, 2700, 1100, 34.65, 0.00),
(153, 1, 2700, 1400, 36.75, 0.00),
(154, 1, 400, 900, 11.02, 0.00),
(155, 1, 400, 1100, 11.55, 0.00),
(156, 1, 400, 1400, 12.60, 0.00),
(157, 1, 400, 1600, 13.12, 0.00),
(158, 1, 400, 1800, 13.65, 0.00),
(159, 1, 400, 2100, 14.17, 0.00),
(160, 1, 400, 2300, 15.22, 0.00),
(161, 1, 400, 2500, 15.75, 0.00),
(162, 1, 400, 2700, 16.80, 0.00),
(163, 1, 700, 900, 13.12, 0.00),
(164, 1, 700, 1100, 14.17, 0.00),
(165, 1, 700, 1400, 14.70, 0.00),
(166, 1, 700, 1600, 15.75, 0.00),
(167, 1, 700, 1800, 16.80, 0.00),
(168, 1, 700, 2100, 17.85, 0.00),
(169, 1, 700, 2300, 18.37, 0.00),
(170, 1, 700, 2500, 19.30, 0.00),
(171, 1, 700, 2700, 23.62, 0.00),
(172, 1, 1100, 900, 17.85, 0.00),
(173, 1, 1100, 1100, 18.90, 0.00),
(174, 1, 1100, 1400, 19.95, 0.00),
(175, 1, 1100, 1600, 21.00, 0.00),
(176, 1, 1100, 1800, 22.05, 0.00),
(177, 1, 1100, 2100, 23.63, 0.00),
(178, 1, 1100, 2100, 24.68, 0.00),
(179, 1, 1100, 2500, 25.73, 0.00),
(180, 1, 1100, 2700, 26.78, 0.00),
(181, 1, 1400, 900, 19.43, 0.00),
(182, 1, 1400, 1100, 21.00, 0.00),
(183, 1, 1400, 1400, 22.05, 0.00),
(184, 1, 1400, 1600, 23.63, 0.00),
(185, 1, 1400, 1800, 24.68, 0.00),
(186, 1, 1400, 2100, 26.25, 0.00),
(187, 1, 1400, 2300, 27.83, 0.00),
(188, 1, 1400, 2500, 29.40, 0.00),
(189, 1, 1400, 2700, 30.45, 0.00),
(190, 1, 1600, 900, 21.53, 0.00),
(191, 1, 1600, 1100, 23.10, 0.00),
(192, 1, 1600, 1400, 24.68, 0.00),
(193, 1, 1600, 1600, 26.25, 0.00),
(194, 1, 2700, 1800, 42.00, 0.00),
(195, 1, 2700, 2100, 44.10, 0.00),
(196, 1, 2700, 2300, 46.73, 0.00),
(197, 1, 2700, 2500, 48.83, 0.00),
(198, 1, 2700, 2700, 51.98, 0.00),
(199, 1, 3000, 900, 34.13, 0.00),
(200, 1, 3000, 1100, 36.75, 0.00),
(201, 1, 3000, 1400, 39.90, 0.00),
(202, 1, 3000, 1600, 42.00, 0.00),
(203, 1, 3000, 1800, 44.63, 0.00),
(204, 1, 3000, 2100, 47.25, 0.00),
(205, 1, 3000, 2300, 50.40, 0.00),
(206, 1, 3000, 2500, 52.50, 0.00),
(207, 1, 3000, 2700, 56.13, 0.00),
(208, 1, 3300, 1600, 44.63, 0.00),
(209, 1, 3300, 1800, 47.25, 0.00),
(210, 1, 3300, 1600, 44.63, 0.00),
(211, 1, 3300, 1800, 47.25, 0.00),
(212, 1, 3300, 2100, 50.40, 0.00),
(213, 1, 3300, 2300, 53.03, 0.00),
(214, 1, 3300, 2500, 55.65, 0.00),
(215, 1, 3300, 2700, 58.28, 0.00),
(216, 1, 1600, 1800, 28.35, 0.00),
(217, 1, 3300, 900, 36.23, 0.00),
(218, 1, 1600, 2100, 29.40, 0.00),
(219, 1, 3300, 1100, 39.38, 0.00),
(220, 1, 3300, 1400, 42.00, 0.00);[/code]

I am running the following query which should return the price 11.02 (Record #93) where as its returning 42. I am not able to find out why this is happening. Any help will be appreciated.

SELECT * FROM prices_content WHERE prcn_pric_id = '2' AND prcn_width >= 400 AND prcn_drop >= 900 LIMIT 1

Thanks.

molona · December 8, 2014, 1:09pm

Maybe you should order your list ascendently.

The thing is that if you limit your query to one result, it will give you the first record it finds, and the very first record it finds will be the very first that was added unless you order your records.

Tapan · December 8, 2014, 1:16pm

Wow thanks, it seems to be working.

molona · December 8, 2014, 1:18pm

I’m glad to hear it… or read it!

system · March 9, 2015, 8:19pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Simple mySQL query returning wrong results

– – Dumping data for table prices_content

–
– Dumping data for table `prices_content`