CODEWITHSUNDEEP
mysqlsql-order-by
midget
midget

Reputation: 106

Order mysql query by repeating sequence of ID

I have a mysql table as follows:

| key | storeID | product |
---------------------------
|  1  | 12      | Apple   |
|  2  | 12      | Banana  |
|  3  |  9      | Orange  |
|  4  |  9      | Durian  |
|  5  | 15      | Melon   |
|  6  | 15      | Berry   |
|  7  |  9      | Grape   |
|  8  | 15      | Tomato  |
|  9  | 12      | Plum    |

Is there a way to order the query such that storeID is output in a repeating sequence as follows?

Desired Output:

| key | storeID | product |
---------------------------
|  3  |  9      | Orange  |
|  1  | 12      | Apple   |
|  5  | 15      | Melon   |
|  4  |  9      | Durian  |
|  2  | 12      | Banana  |
|  6  | 15      | Berry   |  etc

I have searched and found some vague responses which included cross joins with group by's but ended up losing the corresponding product field.

Open to php solution if necessary.

Many thanks for any pointers

EDIT As per advice from @strawberry below and in response to the first responded answer, here is the code for the tables with added rows

CREATE TABLE inventory (`key` INTEGER, `storeID` INTEGER,`product` VARCHAR(6));
    
    INSERT INTO inventory
    (key, storeID, product)
    VALUES
    (1, 12, 'Apple'),
    (2, 12, 'Banana'),
    (3, 9, 'Orange'),
    (4, 9, 'Durian'),
    (5, 15, 'Melon'),
    (6, 15, 'Berry'),
    (7, 9, 'Grape'),
    (8, 15, 'Tomato'),
    (9, 12, 'Plum');

Upvotes: 0

Views: 561

Answers (2)

forpas
forpas

Reputation: 164054

If your version of MySql is 8.0+ you can do it with ROW_NUMBER() window function:

SELECT * 
FROM inventory
ORDER BY ROW_NUMBER() OVER (PARTITION BY storeID ORDER BY `key`),
         storeID

For previous versions of MySql you can use a correlated subquery in the ORDER BY clause:

SELECT t1.* 
FROM inventory t1
ORDER BY (SELECT COUNT(*) FROM inventory t2 WHERE t2.storeID = t1.storeID AND t2.`key` < t1.`key`),
         t1.storeID

See the demo.
Results:

key storeID product
3 9 Orange
1 12 Apple
5 15 Melon
4 9 Durian
2 12 Banana
6 15 Berry
7 9 Grape
9 12 Plum
8 15 Tomato

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

Here's one option for older versions, but it may not scale particularly well...

select x.*
  from inventory x
  join inventory y
    on y.storeid = x.storeid
   and y.id <= x.id
 group
    by x.id
 order
    by count(*)
     , storeid

I've called the identity column 'id' instead of 'key', because reserved words make poor table/column identifiers.

Upvotes: 0

Related Questions