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.
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:
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:
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:
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.
In this tutorial, we have shown you how to emulate the row_number function in MySQL.
'Course > MY-SQL' 카테고리의 다른 글
MySql 계층적 조회 쿼리 (0) | 2015.05.12 |
---|---|
MSSQL에서 MySQL 스토어드 프로시저(Stored PROCEDURE) 변경방법 (0) | 2015.04.30 |
[mysql] 쿼리값이 NULL 일때 0으로 바꾸기 (0) | 2014.09.01 |
[MYSQL] 조인, JOIN (INNER JOIN, OUTER JOIN) (0) | 2014.09.01 |
문자열or텍스트 와 필드 값을 합치는 방법 (0) | 2014.04.25 |