CODEWITHSUNDEEP
mysql
Jeff Monteiro
Jeff Monteiro

Reputation: 765

Mysql how to generate row index (rank) in SELECT?

As result of mysql query, I have this table:

orders | customer |
1      | A        |
1      | A        |
1      | B        |
1      | B        |
1      | B        |

Using mysql only, I need to create a column with index oder ocurrence to each customer to get this table:

orders | customer | index
1      | A        | 1
1      | A        | 2
1      | B        | 1
1      | B        | 2
1      | B        | 3

I try to use this:

set @i=1;
while @i<99999 do
  select 
    count(order_id) as 'orders',
    customer_id as 'customer',
    @i as 'index'
  from
    orders
  set @i= @i+1;     
end while;

But I get an error of statement. Sorry, I have no more idea how to do it. Any idea will be appreciated.

Upvotes: 2

Views: 1099

Answers (2)

N3SS4H
N3SS4H

Reputation: 561

SELECT 
    orders.* , 
    customerOrderCount , 
    IF(@i > 1, @i:= @i - 1, @i:=customerOrdercount) AS orderIndex
FROM (SELECT * FROM orders ORDER BY customer ASC ) AS orders
    JOIN (SELECT customer, count(*) AS customerOrderCount FROM orders GROUP BY 
customer) counts USING (customer)
ORDER BY customer ASC, orderIndex;

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562631

The standard way of doing this with MySQL 8.0 is to use a windowing function:

SELECT orders, customer, 
  ROW_NUMBER() OVER (PARTITION BY customer) AS `index`
FROM orders

Prior to MySQL 8.0, you can do tricks with inline user variables.

SET @i = 0, @c = '';

SELECT 
  orders, 
  IF(@c=customer, @i:=@i+1, @i:=1) AS `index`, 
  @c:=customer AS customer
FROM orders
ORDER BY customer;

Unfortunately, this needs the customer column to be after the index column.

Upvotes: 5

Related Questions