Mysql custom sort

Question

I have a database table containing different category which contains different products and each category having some priority. Suppose cat-1 have five products, cat-2 contains 3 products, cat-3 contains 3 products and cat-4 contains 2 products.

While displaying the products, the order should be as follows.

If categories have same priority (suppose cat-1, cat-2 priority = 1, cat-3 priority = 2, cat-4 priority = NULL), then products will display as follow.

c1p1, c2p1, c1p2, c2p2, c1p3, c2p3, c1p4, c1p5, c3p1, c3p2, c3p3, c4p1, c4p2.

If categories have same priority (suppose cat-1, cat-2 priority = 1, cat-3 and cat-4 priority = 2), then products will display as follow.

c1p1, c2p1, c1p2, c2p2, c1p3, c2p3, c1p4, c1p5, c3p1, c4p1, c3p2, c4p2, c3p3.

If categories have different priority (suppose cat-1 priority = 2, cat-2 priority = 1, cat-3 priority = 3 and cat-4 priority = Null), then products will display as follow.

c2p1, c2p2, c2p3, c1p1, c1p2, c1p3, c1p4, c1p5, c3p1, c3p2, c3p3, c4p1, c4p2.

Here c = category and p = product.

Can this type of sorting is possible in Mysql. Please help.

Here is the structure and sample data of the database tables-

   CREATE TABLE IF NOT EXISTS `categories` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `priority` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ;

   INSERT INTO `categories` (`id`, `name`, `priority`) VALUES
   (1, 'c1', 1),
   (2, 'c2', 1),
   (3, 'c3', 2),
   (4, 'c4', NULL);

   CREATE TABLE IF NOT EXISTS `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `category_id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
   ) ;

INSERT INTO `products` (`id`, `category_id`, `name`) VALUES
(1, 1, 'c1p1'),
(2, 1, 'c1p2'),
(3, 1, 'c1p3'),
(4, 1, 'c1p4'),
(5, 1, 'c1p5'),
(6, 2, 'c2p1'),
(7, 2, 'c2p2'),
(8, 2, 'c2p3'),
(9, 3, 'c3p1'),
(10, 3, 'c3p2'),
(11, 3, 'c3p3'),
(12, 4, 'c4p1'),
(13, 4, 'c4p2');

perhaps you want to order by category.priority asc, product.position_in_category asc, category.id asc? — John Dvorak
– John Dvorak, Commented Dec 17, 2012 at 8:00
Thanks Jack. For the time being i have kept all the data in same table. The fields are cat_id, pro_name, priority. This can be extended into two table later on as categories (cat_id, name, priority) and products( cat_id, pro_name). — Sitansu
– Sitansu, Commented Dec 17, 2012 at 8:42
Hi Jack i have attached a new database structure and sample data. Hope this can help you. — Sitansu
– Sitansu, Commented Dec 17, 2012 at 9:11

fthiella · Accepted Answer · 2012-12-17 10:32:06Z

2

You could create a table with priorities:

create table priorities (
  category varchar(255),
  priority int null);

Then you can select products this way:

select
  products.*
from
  products inner join priorities
  on products.category = priorities.category
order by
  priorities.priority is null, -- this to put null values at the end
  priorities.priority,
  products.id                  -- or some other field

EDIT: This probably is what you are looking for:

select
  products.name
from
  products inner join categories
  on products.category_id = categories.id
order by
  categories.priority is null,
  categories.priority,
  substr(products.name,3),
  categories.name

edited Dec 17, 2012 at 10:32

answered Dec 17, 2012 at 8:17

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

7 Comments

Sitansu Over a year ago

Thanks Fthiella for the help. I have run the query and i am getting the products in the following sequence (c1p1, c1p2, c1p3, c1p4, c1p5, c2p1 ..... c4p2) but here i will expect a sequence as (c1p1, c2p2, c1p2, c2p2, c1p3, c2p3, c1p4, c1p5, c3p1, c3p2, c3p3, c4p1, c4p2.). The sequence is important.

fthiella Over a year ago

@Sitansu you're welcome! i see that you have a category_id already so there's no need to use a LEFT(), but i think it's not clear why c2p2 should come before c1p2 in your first example...

Sitansu Over a year ago

Assume that category 1 and 2 have priority 1, category 3 have priority 2 and category 4 has priority Null.

fthiella Over a year ago

@Sitansu yes, but both c2p2 and c1p2 have different categories but the same priority of 1, and the same product p2, so i don't understand if there's a logic behind this..

Sitansu Over a year ago

Sorry, for the confusion. All the products are different. The sequence is not c1p1, c2p2 its c1p1, c2p1... c1p1 is category's one product one and c2p1 is category's two produce one and so on.

|

Sitansu · Accepted Answer · 2013-01-18 07:14:32Z

Finally i got a better solution. I added an extra field in the products table. The motive is to give a serial number to each category products. My final products table structure look as follows.

CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`sl_no` int(11) NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `products` (`id`, `category_id`, `name`, `sl_no`) VALUES
(1, 1, 'c1p1', 1),
(2, 1, 'c1p2', 2),
(3, 1, 'c1p3', 3),
(4, 1, 'c1p4', 4),
(5, 1, 'c1p5', 5),
(6, 2, 'c2p1', 1),
(7, 2, 'c2p2', 2),
(8, 2, 'c2p3', 3),
(9, 3, 'c3p1', 1),
(10, 3, 'c3p2', 2),
(11, 3, 'c3p3', 3),
(12, 4, 'c4p1', 1),
(13, 4, 'c4p2', 2);

And the query looks like-

SELECT  `Category`.`id` ,  `Category`.`name` ,  `Category`.`priority` ,    `Product`.`name` ,  `Product`.`category_id` 
FROM  `categories` AS  `Category` 
INNER JOIN  `products` AS  `Product` 
WHERE  `Category`.`id` =  `Product`.`category_id` 
ORDER BY CASE WHEN  `Category`.`priority` IS NULL 
THEN 1 
ELSE 0 
END ASC ,  `Category`.`priority` ASC ,  `Product`.`sl_no` ,  `Product`.`category_id`;

Sitansu · Accepted Answer · 2013-01-18 07:27:19Z

This is an indirect solution to get the sequence as per the requirement.

As i was using PHP to fetch data, first i wrote an sql query to fetch the data according to priority.

SELECT `Category`.`id`, `Category`.`name`, `Category`.`priority` , `Product`.`name`, `Product`.`category_id` FROM `categories` AS `Category` INNER JOIN `products` AS `Product` WHERE `Category`.`id` = `Product`.`category_id` ORDER BY CASE WHEN `Category`.`priority` IS NULL THEN 1 ELSE 0 END ASC, `Category`.`priority` ASC;

Then i did a simple array manipulation to get an array as follow.

array(
(int) 1 => array(
    (int) 0 => array(
        (int) 0 => '1',
        (int) 1 => '6'
    ),
    (int) 1 => array(
        (int) 0 => '2',
        (int) 1 => '7'
    ),
    (int) 2 => array(
        (int) 0 => '3',
        (int) 1 => '8'
    ),
    (int) 3 => array(
        (int) 0 => '4'
    ),
    (int) 4 => array(
        (int) 0 => '5'
    ),
),  
(int) 2 => array(
    (int) 0 => array(
        (int) 0 => '9'
    ),
    (int) 1 => array(
        (int) 0 => '10'
    ),
    (int) 2 => array(
        (int) 0 => '11'
    ),      
),
'' => array(
    (int) 0 => array(
        (int) 0 => '12'         
    ),
    (int) 1 => array(
        (int) 0 => '13'

    )
)
)

for series c1p1, c2p1, c1p2, c2p2, c1p3, c2p3, c1p4, c1p5, c3p1, c3p2, c3p3, c4p1, c4p2. Where array keys are priority and leaf values are product ids.

Then i make a string of ids from the above array as follows.

  1,6,2,7,3,8,4,5,9,10,11,12,13

And finally wrote the query as follows.

 SELECT * 
 FROM  `products` 
 WHERE  `id` 
 IN ( 1, 6, 2, 7, 3, 8, 4, 5, 9, 10, 11, 12, 13 ) 
 ORDER BY FIELD( id, 1, 6, 2, 7, 3, 8, 4, 5, 9, 10, 11, 12, 13 );

Collectives™ on Stack Overflow

Mysql custom sort

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related