MySQL row_number 구현

Add row number for each row

To emulate the  row_number function in MySQL, you have to use session variables in the query. The following example selects 5 employees from the  employees table and adds row number for each row, starting from 1.

1 2 3 4 5 6 7

SET @row_number = 0;   SELECT     (@row_number:=@row_number + 1) AS num, firstName, lastName FROM     employees LIMIT 5;

In the query above:

• First, we defined a variable named  row_number and set its value to 0.
• Then, in the query, we increased the value of the  row_number variable to 1 for each row.

Another technique is to use a session variable as a derived table and crossed join it with the main table. See the following query:

1 2 3 4 5

SELECT     (@row_number:=@row_number + 1) AS num, firstName, lastName FROM     employees,(SELECT @row_number:=0) AS t LIMIT 5;

Notice that the derived table must have its own alias to make the query syntactically correct.

Add row number for each group

How about  row_number “over partition by” functionality? For example, what if we want to add row number to each group, and it is reset for every new group. Let’s take a look at the payments table:

1 2 3 4 5

SELECT     customerNumber, paymentDate, amount FROM     payments ORDER BY customerNumber;

Suppose for each customer, we add a row number, and the row number is reset for the new customer. To achieve this, we have to use two session variables, one for the row number and the other for storing the old customer number to compare it with the current one as the following query:

1 2 3 4 5 6 7 8 9 10 11

SELECT     @row_number:=CASE         WHEN @customer_no = customerNumber THEN @row_number + 1         ELSE 1     END AS num,     @customer_no:=customerNumber as CustomerNumber,     paymentDate,     amount FROM     payments ORDER BY customerNumber;

We used CASE statement in the query. If the customer number remains the same, we increase the  row_number variable, otherwise we reset it to 1. The result of the query is as screenshot below.

Like the  row_number for each row, we can use derived table and cross join technique to produce the same result.

1 2 3 4 5 6 7 8 9 10 11

SELECT     @row_number:=CASE         WHEN @customer_no = customerNumber THEN @row_number + 1         ELSE 1     END AS num,     @customer_no:=customerNumber as CustomerNumber,     paymentDate,     amount FROM     payments,(SELECT @customer_no:=0,@row_number:=0) as t ORDER BY customerNumber;

In this tutorial, we have shown you how to emulate the row_number function in MySQL.

출처: http://www.mysqltutorial.org/mysql-row_number/