MySQL-Generating Row Number for each row
Before you get into this article, if you like to read Row Number here it is.
In a development environment as a developer or database administrator may expect the query results to have sequential number or running number like 1,2,3,4, etc for each row. This article explains how Microsoft SQL Server and MySQL behaves to get the work done.
And the purpose of the article is to solve your problems and to save your googling time. Here is the one MySQL could not load assembly solved
Let's get started,
Microsoft SQL Server has in-build function called ROW_NUMBER() picks the sequential order by column name first_name.
Microsoft SQL Server
MySQL
I have done quite a few googling to find the solution, but ended up finding due to mysql lower version the result is not achievable, boom a big no from many web site references. Good News is this article gives you the solution with big YES right below.
There are two methods to solve;
Method 1 : Set a variable and use it in a SELECT statement
Method 2 : Use a variable as a table and cross join it with the source table
The Mysql database version used is 5.5.42, execute the below query to get your version
SELECT @@Version;
Let's get started by step by step
STEP 1: Creating Table
CREATE TABLE Employee
(
EmpId int,
first_name varchar(100),
last_name varchar(100),
city varchar(50)
);
INSERT INTO Employee (EmpId,first_name,last_name,city)
SELECT 1001,'Robert','Brown','London'
UNION
SELECT 1002,'Jack','Sparrow','Hebrwood'
UNION
SELECT 1003,'George','Joseph','Redmond';
SELECT * FROM Employee;
STEP 2:
Method:1 Set a variable and use it in a SELECT statement
Execute the SET command in the query window, this means your setting a variable @row_number to 0.
SET @row_number:=0;
Now execute the below query, to get result with row numbers 1,2,3 etc.
SELECT @row_number:=@row_number+1 AS row_number,
EmpId,
first_name,
last_name,
city
FROM Employee
ORDER BY EmpId;
At this point, you might have a question whey should we use variable and setting to 0?
What happens if don't set variable to 0?
What will be the output of @row_number?
To answer your question @row_number gives the server sequence number which will be unique and every time you run the query @row_number value will keep increasing.
Let's say, you are running the query first time and it has 3 rows, row_number will be 1, 2, 3. The second time you run the same query row_number will be 4,5,6. The third time you run query result will be 7,8,9 as it keeps increasing, obviously this isn't intended output right?
STEP 3:
Method:2 Use a variable as a table and cross join it with the source table
Isn't it possible to get it done with single query? Yup using cross join.
SELECT (@row_number:=@row_number+1) AS row_number,
EmpId,first_name,
last_name,
city
FROM Employee,
(SELECT @row_number:=0) AS x
ORDER BY EmpId;
Note that yellow , highlighted and then placed select statement as assigning @row_number to 0 value.
ood,
If you run your application without next step you will encounter an fatal error @row_number must be defined.
If you run your application without next step you will encounter an fatal error @row_number must be defined.
Important Note on Next Steps
When using @parameter variable in query MySql query browser will give you results, how about .NET or other development environment will fail when executing the query.
So what to do?
Append property called Allow User variable into your MySql connection string as below
You connection string should have similar to this
Database=testdb; Data Source=localhost;User Id=root;Password=**;Allow User Variables =True;
Summary
0 Comments