CODEWITHSUNDEEP
mysqlalgorithmsorting
clueless
clueless

Reputation: 869

How to rotate data among 3 groups?

Let's say I have group A, B and C. Each group has X number of items each. What is the algorithm to used so that I could sort out all the items here in such a way that I take one from each group every time.

IE. Given that A and B have 2 items, and C has 3. I should show a list like this:

 1. A - Item 1
 2. B - Item 1
 3. C - Item 1
 4. A - Item 2
 5. B - Item 2
 6. C - Item 2
 7. C - Item 3

I'm pulling this data from a MySQL DB so if it's at all possible to do this in MySQL, I would appreciate to know.

So what's a good algorithm for this? I would imagine this would be a common scenario for a bunch of things so there should already be some efficient way to do this. I don't know what to Google for though, I'm not getting any good results.

Anyone care to shine some light on this?

Upvotes: 1

Views: 36

Answers (1)

1000111
1000111

Reputation: 13519

You can accomplish such result by the following query:

[You can rotate data among any groups actually]

SELECT 
t.item_type,
t.item_name
FROM 
(
 SELECT 
 item_type,
 item_name,
 IF(@prev = item_type , @rank := @rank + 1 , @rank := 0) rankNumber,
 @prev := item_type
 FROM itemstable , (SELECT @prev := NULL, @rank := 0) vars
 ORDER BY item_type,item_name
) t
ORDER BY t.rankNumber,t.item_type;

SQL FIDDLE DEMO

TEST DATA WITH SCHEMA:

(If you cannot access SQL FIDDLE)

DROP TABLE IF EXISTS `itemstable`;
CREATE TABLE `itemstable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `item_type` varchar(10) CHARACTER SET utf8 NOT NULL,
  `item_name` varchar(50) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`ID`)
);
INSERT INTO `itemstable` VALUES ('1', 'A', 'ITEM 1');
INSERT INTO `itemstable` VALUES ('2', 'A', 'ITEM 2');
INSERT INTO `itemstable` VALUES ('3', 'B', 'ITEM 1');
INSERT INTO `itemstable` VALUES ('4', 'B', 'ITEM 2');
INSERT INTO `itemstable` VALUES ('5', 'C', 'ITEM 1');
INSERT INTO `itemstable` VALUES ('6', 'C', 'ITEM 2');
INSERT INTO `itemstable` VALUES ('8', 'C', 'ITEM 3');

Running the above query on this test data you will get an output like below:

item_type     item_name
A              ITEM 1
B              ITEM 1
C              ITEM 1
A              ITEM 2
B              ITEM 2
C              ITEM 2
C              ITEM 3

Explanation:

  • First sort your data according to ITEM_TYPE (A,B,C...) and then again sort this sorted data based on ITEM_NAME (ITEM1,ITEM2...).
 item_type   item_name
   A          ITEM 1
   A          ITEM 2
   B          ITEM 1
   B          ITEM 2
   C          ITEM 1
   C          ITEM 2
   C          ITEM 3
  • So that the items under the same type stick together having a sorted order among them.
  • Now imagine you are walking through this double sorted data .
  • If you find an item belonging to a previously seen ITEM_TYPE then you will assign an incremental rank number to this just seen item.
  • Otherwise, (that means you have just seen an item belonging to a new ITEM_TYPE) you will assign a rank number which is zero.

Upvotes: 1

Related Questions