MySQL how to give a row a defined number

Question

ok, kind of finding it hard to give this question a proper title as im not really sure what you would call this function.

So, i have a table called categories, it contains ID, ParentID, Name, OrderNo plus a few more.

What i want to do is Give each category an OrderNo, by order number i mean if my category tree is as follows:

Computers
Computers > Laptops
Computers > Desktops
Computers > Components > Hard Drives
Computers > Components > Monitors
TVs
TVs > LED
TVs > LCD

So i simply want to give each category a value for OrderNo that starts from 1. So

Computers 1
Computers > Laptops 1
Computers > Desktops 2
Computers > Components 3
Computers > Components > Hard Drives 1
Computers > Components > Monitors 2
TVs 2
TVs > LED 1
TVs > LCD 2

Hopefully this makes more sense that it does trying to explain what i am trying to do. Unfortunatly i have no idea what such a function would be called, else i would google it.

My initial select statement would look something like:

select ID,ParentID,Name,OrderNo
from categories
Order by ParentID,ID

But no idea how to advance it further. Sorry for the rubbish explanation Thanks in advance John

Maybe you can find something helpful here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — Barmar, Oct 02 '13 at 19:07
Have a look at this answer: http://stackoverflow.com/a/8177307/438971 - you can use the technique with `CONCAT()` and `IF()` with the running numbers to get a partitioned row number if you partition by the `ParentID` (keep the hierarchy for that query as a comma separated list of that). — Orbling, Oct 02 '13 at 19:21
@Barmar: that link is often given, but it misses out the possibilities of path-enumeration and closure tables ([see Bill Karwin's slides](http://www.slideshare.net/billkarwin/models-for-hierarchical-data)) — Wrikken, Oct 02 '13 at 19:45

score 2 · Answer 1 · answered Oct 02 '13 at 19:40

SET @parent=0;
SET @sort=1;
UPDATE categories 
SET OrderNo = (@sort := IF(
       ParentID <=> @parentid,
       @sort+1,
       1 + IF(@parentid := ParentID,0,0)
))
ORDER BY parentid;

So convoluted that you might want to do it in application code instead...

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

If you wanted to generate it on the fly in a SELECT, you could use the following query. It could be used as input to an UPDATE query to set the values. Making it a single line version of Wrikken's answer.

SQLFiddle: http://sqlfiddle.com/#!2/c41b5/8 (wrapped version: http://sqlfiddle.com/#!2/c41b5/13)

SELECT c.`ID`,
       c.`ParentID`,
       c.`Name`,
       @running := IF(@prevParentID <=> c.`ParentID`, @running + 1, 1) AS `OrderNoCalc`,
       @prevParentID := c.`ParentID` AS `prevParentID`
FROM `Categories` c,
     (SELECT @running := 0) r,
     (SELECT @prevParentID := NULL) p
ORDER BY c.`ParentID`, c.`Name`

Assuming your schema is like this:

CREATE TABLE `Categories` (
  `ID` int(10) NOT NULL auto_increment,
  `ParentID` int(10) default NULL,
  `Name` varchar(32) NOT NULL default '',
  `OrderNo` int(10) default NULL,
  PRIMARY KEY (`ID`),
  KEY `ParentID` (`ParentID`),
  KEY `OrderNo` (`ParentID`, `OrderNo`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

INSERT INTO `Categories` VALUES (1, NULL, 'Computers', NULL);
INSERT INTO `Categories` VALUES (2, NULL, 'TVs', NULL);
INSERT INTO `Categories` VALUES (3, 1, 'Laptops', NULL);
INSERT INTO `Categories` VALUES (4, 1, 'Desktops', NULL);
INSERT INTO `Categories` VALUES (5, 1, 'Components', NULL);
INSERT INTO `Categories` VALUES (6, 5, 'Hard Drives', NULL);
INSERT INTO `Categories` VALUES (7, 5, 'Monitors', NULL);
INSERT INTO `Categories` VALUES (8, 2, 'LCD', NULL);
INSERT INTO `Categories` VALUES (9, 2, 'LED', NULL);

edited May 23 '17 at 12:21

Community

1
1

answered Oct 02 '13 at 19:49

Orbling

20,413
3
53
64

thanks for this. Gave it a try and it seems to have done the trick. Yes, it is going to be part of an export script so no need for it to be set into the database or anything. However, it is going to get complicated as i am selecting about 15 columns of which a few are already joins, so how would i work this into the equation, and do i need to include @prevParentID := c.`ParentID` AS `prevParentID` as its not a value i want to include in my export – user2231688 Oct 02 '13 at 19:55
1

Yeah, the problem is I can't use the `JOIN (SELECT @var)` trick in `UPDATE`'s apparently ;). You can [borrow the NULL-safe comparison though](http://sqlfiddle.com/#!2/c41b5/8): `@prevParentID = c.ParentID OR (@prevParentID IS NULL AND c.ParentID IS NULL)` => `@prevParentID <=> c.ParentID` as (`NULL<=>NULL` is `true`) – Wrikken Oct 02 '13 at 19:55
@user2231688: That is an essential part of the query, as the presence in the `SELECT` of the assignment needs to go somewhere. You could wrap it in an outer SELECT to extract just the values you wanted, perhaps the assignment could be buried in some function value that was ignored. – Orbling Oct 02 '13 at 20:00
1

@Wrikken: I did not know about the `<=>` operator, very useful, thanks! – Orbling Oct 02 '13 at 20:01
Yep, burying the statement like I did `IF( the statement you want handled, what you were going to select anyway, what you were going to select anyway)` (same result on true/false in the `IF`. – Wrikken Oct 02 '13 at 20:03
1

Actually, just `SELECT @prevParentID := c.ParentID AS parentID` would [suffice here it seems](http://sqlfiddle.com/#!2/c41b5/12) as long as you run if _after_ the `OrderNoCalc` result. – Wrikken Oct 02 '13 at 20:06
@Wrikken: Quite right, because of the single field - I started off adapting it from my usual technique with a multi-field key. – Orbling Oct 02 '13 at 20:10
SELECT a.ID, a.ParentID, a.Name, a.OrderNo, a.IsHidden, COUNT( c.CategoryID ) AS ProductCount, a.SubCategoryProductCount, a.ThumbImageURL, a.NestLevel, COUNT(b.ParentID) AS SubCategoryCount, a.UNSPSC, a.TypeID, a.MainImageURL, a.MetaTitle, a.MetaDescription, a.Description FROM categories a LEFT JOIN categories b ON a.ID = b.ParentID LEFT JOIN productcategories c ON a.ID = c.CategoryID Group BY a.ID,a.Name,a.ParentID – user2231688 Oct 02 '13 at 20:10
@user2231688: I included a link of how one would wrap it, to make the table come out as it should look if filled in. – Orbling Oct 02 '13 at 20:10
Oh yes, just having a look now – user2231688 Oct 02 '13 at 20:11
@user2231688: If you add the other fields you have on `categories` to that sort of query above, and use it in the `FROM` as `a`, instead of just using `categories` in that example, it should generate correctly. As the task is to get `OrderNo` to be that calculated value. You could always run Wrikken's `UPDATE` code if you want the values in the database, you have the field in the table it seems. Just run the update before issuing the query you have. – Orbling Oct 02 '13 at 20:14
@Orbling Doesnt need to be in the database to be honest. Sorry for being dumb, but what do you mean exactly by " use it in the FROM as a, instead of just using categories" – user2231688 Oct 02 '13 at 20:23
@user2231688: Instead of having `FROM categories a` use `FROM (...SQL from above with all fields of categories...) a`. – Orbling Oct 02 '13 at 21:05
1

@Orbling Thanks very much for your help. Its getting late so will give your last comment a go in the morning. Hopefully i understood it correctly..heres hoping – user2231688 Oct 02 '13 at 21:12

MySQL how to give a row a defined number

2 Answers2