Clever Geek Handbook

How to get ordered result set when used in mysql keyword

I am trying to execute the below query to get the ordered data from the category_child table and by extension from the categories table. select * from the category the id is in (select child_id from the category_child where category_id = 1 order by sequence);

Like it

select * from category where id in (2,3,4);

and

select * from category where id in (3,2,4);

give me the same result. Is there any way to get the result in the same order.

category and categories_child:

-- Table structure for table `category`
--

DROP TABLE IF EXISTS `category`;<br/>
/*!40101 SET @saved_cs_client     = @@character_set_client */;<br/>
/*!40101 SET character_set_client = utf8 */;<br/>
CREATE TABLE `category` (<br/>
  `id` int(11) NOT NULL AUTO_INCREMENT,<br/>
  `name` VARCHAR(50) NOT NULL,  <br/>
  `description` VARCHAR(250) NOT NULL,<br/>
  `image_url` VARCHAR(250),<br/>
  `created_on` timestamp NOT NULL DEFAULT '2014-11-06 00:00:00',<br/>
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  <br/>
  PRIMARY KEY (`id`)<br/>
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;<br/>
/*!40101 SET character_set_client = @saved_cs_client */;<br/>

--
-- Table structure for table `category_child`<br/>
--

DROP TABLE IF EXISTS `category_child`;<br/>
/*!40101 SET @saved_cs_client     = @@character_set_client */;<br/>
/*!40101 SET character_set_client = utf8 */;<br/>
CREATE TABLE `category_child` (<br/>
  `id` int(11) NOT NULL AUTO_INCREMENT,<br/>
  `category_id` int(11) NOT NULL,<br/>
  `child_id` int(11) NOT NULL,<br/>
  `child_type` VARCHAR(100) NOT NULL,<br/>
  `sequence` int(4) NOT NULL,<br/>
  `created_on` timestamp NOT NULL DEFAULT '2014-11-06 00:00:00',<br/>
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  <br/>
  PRIMARY KEY (`id`)<br/>
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;<br/>
/*!40101 SET character_set_client = @saved_cs_client */;<br/>
+3


source to share


4 answers


next request 

select c.* 
from category c, category_child cc 
where cc.category_id=1 and c.id=cc.child_id 
order by cc.sequence;


will work, only a small change in state in the response given by Nir-Z and the connection is dropped.

+2


source


Why not do



Select c.* 
from category c 
join category_child cc on cc.id=c.category_id 
where cc.category_id=1 
order by cc.sequence
+1


source


If you try to order them by category_child.sequence

, then they will be returned in any order when the used index returns results category

.

So @ Nir-Z's answer will give you the results sorted in order category_child.sequence

, but if there category

were multiple indices on, you might get different results.

The only way to ensure consistent results every time is to provide order for category

.

Select c.* 
from category c 
join category_child cc on cc.category_id=c.id 
where cc.category_id=1 
order by cc.sequence, c.name

c.name

can be any of the fields from category

.

+1


source


You cannot control the order of the result with a clause where

(at least not deterministically). Instead, you should just add an explicit sentence order by

:

select * from category where id in (2,3,4) order by id;

select * from category where id in (3,2,4) order by id;
0


source






More articles:


All Articles