Jump to content

MySQL sorting problem

sniperscope

By sniperscope
in MySQL Help

 Share

Recommended Posts

sniperscope Regular Member

sniperscope

Posted
Posted

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:

 

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

ID | SHOP NAME |  PAYMENT_PLAN  |  ADD_DATE  | ADD_TIME |

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

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

 

 

Link to comment
https://forums.phpfreaks.com/topic/254761-mysql-sorting-problem/
Share on other sites
sniperscope Regular Member

sniperscope

Posted
  • Author
Posted

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.

 

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

ID | SHOP NAME |  PAYMENT_PLAN  |  ADD_DATE  | ADD_TIME |

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

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 Member

kickstart

Posted
Posted

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 Regular Member

sniperscope

Posted
  • Author
Posted

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 Member

kickstart

Posted
Posted

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

 

kickstart Member

kickstart

Posted
Posted

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 Regular Member

sniperscope

Posted
  • Author
Posted

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', '[email protected]', 'kansai', 'osaka', 'nipponbashi', 'osaka city', 'osaka chamber of commerce', '21074', 'yes', 3, '12/20'),
(2, 'shop_b', '[email protected]', 'kansai', 'osaka', 'umeda', 'osaka city', 'osaka chamber of commerce', '11395', 'yes', 2, '12/20'),
(3, 'shop_c', '[email protected]', 'kansai', 'osaka', 'kyobashi', 'osaka city', 'osaka chamber of commerce', '14656', 'yes', 1, '12/20'),
(4, 'shop_d', '[email protected]', 'kansai', 'osaka', 'tsukamoto', 'osaka city', 'osaka chamber of commerce', '45742', 'yes', 3, '12/20'),
(5, 'shop_e', '[email protected]', 'kansai', 'osaka', 'nipponbashi', 'osaka city', 'osaka chamber of commerce', '19292', 'yes', 2, '12/20'),
(6, 'shop_f', '[email protected]', 'kansai', 'osaka', 'umeda', 'osaka city', 'osaka chamber of commerce', '11902', 'yes', 3, '12/20'),
(7, 'shop_g', '[email protected]', 'kansai', 'osaka', 'tani9', 'osaka city', 'osaka chamber of commerce', '29796', 'yes', 2, '12/20'),
(8, 'shop_h', '[email protected]', 'kansai', 'osaka', 'nipponbashi', 'osaka city', 'osaka chamber of commerce', '19311', 'yes', 1, '12/20'),
(9, 'shop_i', '[email protected]', 'kansai', 'osaka', 'kyoto', 'osaka city', 'osaka chamber of commerce', '2-7-1-307', 'yes', 2, '12/20'),
(10, 'shop_j', '[email protected]', 'kansai', 'osaka', 'kyoto', 'osaka city', 'osaka chamber of commerce', '73358', 'yes', 3, '12/20'),
(11, 'shop_k', '[email protected]', 'kansai', 'osaka', 'nipponbashi', 'osaka city', 'osaka chamber of commerce', '20449', 'yes', 3, '12/20'),
(12, 'shop_l', '[email protected]', 'kansai', 'osaka', 'kyoto', 'osaka city', 'osaka chamber of commerce', '2-7-1-59', 'yes', 2, '12/20'),
(13, 'shop_m', '[email protected]', 'kansai', 'osaka', 'kyoto', 'osaka city', 'osaka chamber of commerce', '2-7-1-59', 'yes', 1, '12/20'),
(14, 'shop_n', '[email protected]', 'kansai', 'osaka', 'kobe', 'osaka city', 'osaka chamber of commerce', '18-17', 'yes', 1, '12/20'),
(15, 'shop_o', '[email protected]', 'kansai', 'osaka', 'kobe', 'osaka city', 'osaka chamber of commerce', '18-16', 'yes', 2, '12/20'),
(16, 'shop_p', '[email protected]', 'kansai', 'osaka', 'kobe', 'osaka city', 'osaka chamber of commerce', '18-18', 'yes', 2, '12/20'),
(17, 'shop_q', '[email protected]', 'kansai', 'osaka', 'tennoji', 'osaka city', 'osaka chamber of commerce', '18-54', 'yes', 3, '12/20'),
(18, 'shop_r', '[email protected]', 'kansai', 'osaka', 'tennoji', 'osaka city', 'osaka chamber of commerce', '18-62', 'yes', 3, '12/20'),
(19, 'shop_s', '[email protected]', 'kansai', 'osaka', 'tennoji', 'osaka city', 'osaka chamber of commerce', '18-48', 'yes', 3, '12/20'),
(20, 'shop_t', '[email protected]', 'kansai', 'osaka', 'umeda', 'osaka city', 'osaka chamber of commerce', '11814', 'yes', 3, '12/20'),
(21, 'shop_u', '[email protected]', 'kansai', 'osaka', 'tsukamoto', 'osaka city', 'osaka chamber of commerce', '45240', 'yes', 2 '12/20'),
(22, 'shop_y', '[email protected]', 'kansai', 'osaka', 'umeda', 'osaka city', 'osaka chamber of commerce', '12202', 'yes', 1, '12/20'),
(23, 'shop_z', '[email protected]', '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 Member

kickstart

Posted
Posted

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.