MySQL sorting problem

sniperscope · January 11, 2012

Hi

I am doing something but i am stuck and i need some help.

I have 2 tables one is holding shop information, other one is holding upgrade information.

shops:

CREATE TABLE shops (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  shop_name varchar(250) NOT NULL,
  shop_type varchar(50) NOT NULL,
  work_hour varchar(15) DEFAULT '00:00～00:00',
  shop_phone varchar(30) DEFAULT NULL,
  shop_mail varchar(100) NOT NULL,
  shop_address_long text
  qualification varchar(200) DEFAULT '--',
  shop_url_pc varchar(200) DEFAULT NULL,
  shop_url_mobile varchar(200) DEFAULT NULL,
  publication enum('yes','no') NOT NULL,
  payment_plan tinyint(1) NOT NULL,
  shop_login_id varchar(30) NOT NULL,
  shop_login_pass varchar( NOT NULL,
  register_date varchar(5) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY shop_name (shop_name),
  UNIQUE KEY shop_login_id (shop_login_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Shops data stored';

search_up:

CREATE TABLE search_up (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  shop_id int(6) NOT NULL,
  add_date int(10) NOT NULL,
  add_time int(10) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

I want to get output something like:

----+---------------+-----------------------+----------------+--------------+

----+---------------+-----------------------+----------------+--------------+

11 | shop_a | gold | 2012-01-12 | 11:20 |

19 | shop_f | gold | 2012-01-12 | 11:00 |

23 | shop_m | gold | 2012-01-11 | 23:30 |

51 | shop_d | gold | 2012-01-10 | 16:08 |

14 | shop_l | gold | 2012-01-09 | 13:25 |

----+---------------+-----------------------+----------------+-------------+

5 | shop_y | silver | 2012-01-12 | 11:21 |

9 | shop_x | silver | 2012-01-12 | 08:51 |

21 | shop_z | silver | 2012-01-11 | 15:20 |

73 | shop_p | silver | 2012-01-10 | 22:31 |

59 | shop_h | silver | 2012-01-03 | 11:15 |

----+---------------+-----------------------+----------------+-------------+

41 | shop_l | basic | 2012-01-12 | 11:19 |

38 | shop_i | basic | 2012-01-12 | 10:43 |

i am using mysql command below but it sorts only based on plans

SELECT s.id, s.shop_name, s.payment_plan, ss.add_date, ss.add_time FROM shops s LEFT JOIN ( SELECT ss.shop_id, MAX(add_date) AS add_date, MAX(add_time) AS add_time FROM search_up ss GROUP BY shop_id ) ss ON s.id = ss.shop_id WHERE s.shop_region = 'kansai' AND s.publication = 'yes' ORDER BY s.payment_plan ASC, ss.add_date DESC, ss.add_time DESC

Could someone please show me what am i doing wrong ?

Regards

kickstart · January 11, 2012

Hi

Can't see anything obvious.

Can you paste an example of the actual output you get?

All the best

Keith

The Little Guy · January 11, 2012

I don't know what your output is, but max may be the root of your problem.

Just because your using the max date doesn't mean that the return value of max is associated with the row that comes back.

kickstart · January 11, 2012

Hi

:facepalm:

Yep, above is probably correct.

As it is at the moment for each shop id your subselect will bring back the max date and the max time rather than the max time within the max date

All the best

Keith

sniperscope · January 12, 2012

Hi

Can't see anything obvious.

Can you paste an example of the actual output you get?

All the best

Keith

Hi

What is actual output is sorting by ID as below. Date and Time seems ignoring.

----+---------------+-----------------------+----------------+--------------+

----+---------------+-----------------------+----------------+--------------+

11 | shop_a | gold | 2012-01-12 | 11:20 |

14 | shop_l | gold | 2012-01-09 | 13:25 |

19 | shop_f | gold | 2012-01-12 | 11:00 |

23 | shop_m | gold | 2012-01-11 | 23:30 |

51 | shop_d | gold | 2012-01-10 | 16:08 |

----+---------------+-----------------------+----------------+-------------+

5 | shop_y | silver | 2012-01-12 | 11:21 |

9 | shop_x | silver | 2012-01-12 | 08:51 |

21 | shop_z | silver | 2012-01-11 | 15:20 |

59 | shop_h | silver | 2012-01-03 | 11:15 |

73 | shop_p | silver | 2012-01-10 | 22:31 |

----+---------------+-----------------------+----------------+-------------+

38 | shop_i | basic | 2012-01-12 | 10:43 |

41 | shop_l | basic | 2012-01-12 | 11:19 |

kickstart · January 12, 2012

Hi

Can't see any reason for that.

Possibly need the table layouts and sample data to do a bit of checking.

To correct the problem with not getting the latest times for the latest days the following should help:-

SELECT s.id, s.shop_name, s.payment_plan, ss.add_date, sss.add_time 
FROM shops s 
LEFT JOIN ( SELECT ss.shop_id, MAX(add_date) AS add_date FROM search_up ss GROUP BY shop_id ) ss ON s.id = ss.shop_id 
LEFT JOIN ( SELECT ss.shop_id, add_date, MAX(add_time) AS add_time FROM search_up ss GROUP BY shop_id, add_date ) sss ON s.id = ss.shop_id AND ss.add_date = sss.add_date
WHERE s.shop_region = 'kansai' 
AND s.publication = 'yes' 
ORDER BY s.payment_plan ASC, ss.add_date DESC, sss.add_time DESC

All the best

Keith

sniperscope · January 12, 2012

Dear Kickstart

I really appreciate for your query.

That query block bring one record(from shops table) and show it 107 times(in my case search_up table has 107 records).

I am going to one more thing but not sure about performance. Also personally i hate doing tasks Easy/Lazy way.

What am i thinking is search_up table holds only date and time and i need only latest records for each shops.

So, create new two columns in shops table and give them "add_date" and "add_time" and sort it by

ORDER BY payment_plan ASC, add_date ASC, add_time ASC

Don't you think this works(actually i tested it and it works) and it is lazy way?

kickstart · January 12, 2012

Hi

Problem is as it stands you will get the maximum time, irrespective of whether that time was on the maximum date

Ie, say you had 3 records

2012/01/11 - 10:00

2012/01/11 - 10:30

2012/01/01 - 11:00

Getting the max date will bring back 2012/01/11 (as you want) but the max time will be 11:00 which is from a record which does not have the max date.

Your solution might well work but it is (as you say) a bit lazy and leads to a load of duplicated data.

Can you export the table declares and the sample data? If you can do this I can attempt to try out the SQL I have suggested.

All the best

Keith

sniperscope · January 12, 2012

Okay

Give me a few minutes and i dump some sample data.

About duplicating records. How about add " GROUP BY shop_id " into query.

Then it will group by shop_id right?

kickstart · January 12, 2012

Hi

It should, but should also not be necessary with the code I was trying to come up with!

MySQL is a bit more tolerant of it, but most flavors of SQL require all non aggregate columns to be in the group by clause. This is still a good idea in MySQL to avoid some slightly strange results.

All the best

Keith

sniperscope · January 12, 2012

Dear kickstart

Here is dump of shops table:

DROP TABLE IF EXISTS `shops`;
CREATE TABLE IF NOT EXISTS `shops` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_name` varchar(250) NOT NULL,
  `shop_mail` varchar(100) NOT NULL,
  `shop_region` varchar(20) DEFAULT NULL,
  `shop_state` varchar(20) DEFAULT NULL,
  `shop_area` varchar(50) DEFAULT NULL,
  `shop_address_long` text,
  `chamber` varchar(200) DEFAULT NULL,
  `chamber_id` varchar(200) DEFAULT NULL,
  `publication` enum('yes','no') NOT NULL,
  `payment_plan` tinyint(1) NOT NULL COMMENT '1 = Gold, 2 = Silver, 3 = Basic',
  `register_date` varchar(5) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shop_name` (`shop_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Shops data stored' AUTO_INCREMENT=33 ;

--
-- Dumping data for table `shops`
--

INSERT INTO `shops` (`id`, `shop_name`, `shop_mail`, `shop_region`, `shop_state`, `shop_area`, `shop_address_long`, `chamber`, `chamber_id`, `publication`, `payment_plan`, `register_date`) VALUES
(1, 'shop_a', 'something@examle.com', 'kansai', 'osaka', 'nipponbashi', 'osaka city', 'osaka chamber of commerce', '21074', 'yes', 3, '12/20'),
(2, 'shop_b', 'something@examle.com', 'kansai', 'osaka', 'umeda', 'osaka city', 'osaka chamber of commerce', '11395', 'yes', 2, '12/20'),
(3, 'shop_c', 'something@examle.com', 'kansai', 'osaka', 'kyobashi', 'osaka city', 'osaka chamber of commerce', '14656', 'yes', 1, '12/20'),
(4, 'shop_d', 'something@examle.com', 'kansai', 'osaka', 'tsukamoto', 'osaka city', 'osaka chamber of commerce', '45742', 'yes', 3, '12/20'),
(5, 'shop_e', 'something@examle.com', 'kansai', 'osaka', 'nipponbashi', 'osaka city', 'osaka chamber of commerce', '19292', 'yes', 2, '12/20'),
(6, 'shop_f', 'something@examle.com', 'kansai', 'osaka', 'umeda', 'osaka city', 'osaka chamber of commerce', '11902', 'yes', 3, '12/20'),
(7, 'shop_g', 'something@examle.com', 'kansai', 'osaka', 'tani9', 'osaka city', 'osaka chamber of commerce', '29796', 'yes', 2, '12/20'),
(8, 'shop_h', 'something@examle.com', 'kansai', 'osaka', 'nipponbashi', 'osaka city', 'osaka chamber of commerce', '19311', 'yes', 1, '12/20'),
(9, 'shop_i', 'something@examle.com', 'kansai', 'osaka', 'kyoto', 'osaka city', 'osaka chamber of commerce', '2-7-1-307', 'yes', 2, '12/20'),
(10, 'shop_j', 'something@examle.com', 'kansai', 'osaka', 'kyoto', 'osaka city', 'osaka chamber of commerce', '73358', 'yes', 3, '12/20'),
(11, 'shop_k', 'something@examle.com', 'kansai', 'osaka', 'nipponbashi', 'osaka city', 'osaka chamber of commerce', '20449', 'yes', 3, '12/20'),
(12, 'shop_l', 'something@examle.com', 'kansai', 'osaka', 'kyoto', 'osaka city', 'osaka chamber of commerce', '2-7-1-59', 'yes', 2, '12/20'),
(13, 'shop_m', 'something@examle.com', 'kansai', 'osaka', 'kyoto', 'osaka city', 'osaka chamber of commerce', '2-7-1-59', 'yes', 1, '12/20'),
(14, 'shop_n', 'something@examle.com', 'kansai', 'osaka', 'kobe', 'osaka city', 'osaka chamber of commerce', '18-17', 'yes', 1, '12/20'),
(15, 'shop_o', 'something@examle.com', 'kansai', 'osaka', 'kobe', 'osaka city', 'osaka chamber of commerce', '18-16', 'yes', 2, '12/20'),
(16, 'shop_p', 'something@examle.com', 'kansai', 'osaka', 'kobe', 'osaka city', 'osaka chamber of commerce', '18-18', 'yes', 2, '12/20'),
(17, 'shop_q', 'something@examle.com', 'kansai', 'osaka', 'tennoji', 'osaka city', 'osaka chamber of commerce', '18-54', 'yes', 3, '12/20'),
(18, 'shop_r', 'something@examle.com', 'kansai', 'osaka', 'tennoji', 'osaka city', 'osaka chamber of commerce', '18-62', 'yes', 3, '12/20'),
(19, 'shop_s', 'something@examle.com', 'kansai', 'osaka', 'tennoji', 'osaka city', 'osaka chamber of commerce', '18-48', 'yes', 3, '12/20'),
(20, 'shop_t', 'something@examle.com', 'kansai', 'osaka', 'umeda', 'osaka city', 'osaka chamber of commerce', '11814', 'yes', 3, '12/20'),
(21, 'shop_u', 'something@examle.com', 'kansai', 'osaka', 'tsukamoto', 'osaka city', 'osaka chamber of commerce', '45240', 'yes', 2 '12/20'),
(22, 'shop_y', 'something@examle.com', 'kansai', 'osaka', 'umeda', 'osaka city', 'osaka chamber of commerce', '12202', 'yes', 1, '12/20'),
(23, 'shop_z', 'something@examle.com', 'kansai', 'osaka', 'nipponbashi', 'osaka city', 'osaka chamber of commerce', '20625', 'yes', 2, '12/20');

And search_up table:

CREATE TABLE IF NOT EXISTS `search_up` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` int(6) NOT NULL,
  `add_date` int(10) NOT NULL,
  `add_time` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=208 ;

--
-- Dumping data for table `search_up`
--

INSERT INTO `search_up` (`id`, `shop_id`, `add_date`, `add_time`) VALUES
(1, 22, 1324825200, 943933200),
(2, 23, 1324825200, 943940760),
(17, 1, 1325084400, 943901640),
(18, 2, 1325084400, 943902240),
(19, 1, 1325084400, 943902780),
(20, 3, 1325084400, 943902840),
(21, 3, 1325084400, 943902840),
(22, 4, 1325084400, 943903500),
(23, 5, 1325084400, 943904040),
(24, 6, 1325084400, 943904520),
(25, 7, 1325084400, 943904760),
(26, 8, 1325084400, 943905060),
(27, 9, 1325084400, 943905360),
(28, 10, 1325084400, 943905840),
(29, 11, 1325084400, 943906140),
(30, 12, 1325084400, 943907160),
(31, 13, 1325084400, 943915140),
(33, 17, 1325084400, 943917120),
(34, 18, 1325084400, 943917720),
(35, 19, 1325084400, 943918260),
(36, 20, 1325084400, 943918980),
(59, 9, 1325170800, 943948560),
(60, 13, 1325170800, 943949160),
(61, 23, 1325257200, 943896180),
(62, 23, 1325257200, 943896180),
(63, 23, 1325257200, 943899540),
(64, 4, 1325257200, 943955280),
(65, 6, 1325257200, 943955460),
(68, 1, 1325257200, 943955880),
(77, 19, 1325602800, 943956180),
(80, 4, 1325602800, 943956360),
(81, 7, 1325602800, 943956420),
(94, 12, 1325689200, 943965600),
(96, 4, 1325775600, 943942620),
(97, 12, 1325775600, 943942680),
(102, 17, 1325775600, 943948080),
(103, 1, 1325775600, 943951680),
(106, 10, 1325775600, 943960200),
(107, 2, 1325775600, 943960320),
(112, 7, 1325775600, 943972560),
(113, 9, 1325775600, 943972620),
(114, 5, 1325775600, 943972620),
(121, 12, 1326034800, 943969320),
(125, 22, 1326121200, 943928280),
(128, 5, 1326121200, 943949580),
(129, 19, 1326121200, 943949640),
(130, 7, 1326121200, 943949700),
(133, 21, 1326207600, 943931220),
(134, 22, 1326207600, 943933500),
(135, 20, 1326207600, 943933620),
(139, 20, 1326207600, 943952520),
(160, 5, 1326294000, 943924980),
(161, 19, 1326294000, 943925040),
(164, 6, 1326294000, 943925280),
(196, 17, 1326294000, 943950780);

kickstart · January 12, 2012

Hi

Spotted the problem with my SQL.

SELECT s.id, s.shop_name, s.payment_plan, ss.add_date, sss.add_time

FROM shops s

LEFT JOIN ( SELECT ss.shop_id, MAX(add_date) AS add_date FROM search_up ss GROUP BY shop_id ) ss ON s.id = ss.shop_id

LEFT JOIN ( SELECT ss.shop_id, add_date, MAX(add_time) AS add_time FROM search_up ss GROUP BY shop_id, add_date ) sss ON s.id = sss.shop_id AND ss.add_date = sss.add_date

WHERE s.shop_region = 'kansai'

AND s.publication = 'yes'

ORDER BY s.payment_plan ASC, ss.add_date DESC, sss.add_time DESC

Add the extra s highlighted in red.

Note that this is going to bring back rows for shops which have no matching records on the search_up table. If you don't want these then change them from LEFT JOINs to OUTER JOINs

All the best

Keith

sniperscope · January 13, 2012

Dear kickstart

Thanks for help and sorry for late reply.

I just tested your query. It is marvelous.

You cannot imagine how i appreciate. You saved my at least 3 days.

Thank you soooooooo much.

Sign In

MySQL sorting problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information