CODEWITHSUNDEEP
mysql
Alvin SIU
Alvin SIU

Reputation: 1042

How to generate sequence number in MySQL view?

I am using MySQL 5.6 under Linux.

I have a table to let user input a from-number and a to-number.

Then, there is a view to select some records from another table with account numbers between the from-number and the to-number.

The most difficult problem is that user wants a SEQUENCE number for each records in the view, starting from 1. For example, if the view shows 37 rows, the sequence number should starts from 1, 2, 3, ... until 37, no jumping number. The sorting order of the view is not important.

I know that there is auto-increment column in MySQL table. But, for my case, I have to use a VIEW, not a table.

Does anyone know how to do so ?

BTW, I have to use a VIEW to do so, not a SELECT statement. User does not know how to input SELECT statement, but they know how to click the view to look at the view.

Upvotes: 1

Views: 10148

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

BTW, I have to use a VIEW to do so, not a SELECT statement. User does not know how to input SELECT statement, but they know how to click the view to look at the view.

Technically you want something like this to simulate ranking or a row number.. 

CREATE VIEW table_view 
AS
 SELECT
  *
  , (@row_number := @row_number + 1) AS row_number 
 FROM 
  table
 # Because a SQL table is a unsorted set off data ORDER BY is needed to get stabile ordered results.
 ORDER BY
  table.column ASC 
CROSS JOIN (SELECT @row_number := 0) AS init_user_var

You can't use this SQL code you will get the error below if you try to create a View with a user variable. 

Error Code: 1351
View's SELECT contains a variable or parameter

The SQL code below also makes it possible to generate the row_number. This assumes that you have a id column what is generated with AUTO_INCREMENT. But the subquery is a correlated subquery what makes the execution very slow on larger tables because the counting need to be executed on every record.

CREATE VIEW table_view
AS
 SELECT 
  *
  , (SELECT COUNT(*) + 1 FROM table inner WHERE inner.id < outer.id) AS row_number
 FROM 
   table outer

MySQL 8.0+ Only.

MySQL supports window functions so no MySQL´s user variables are needed to simulate ranking or a row number.

CREATE VIEW table_view 
AS
 SELECT
  *
 # Because a SQL table is a unsorted set off data ORDER BY is needed to get stabile ordered results.
  , (ROW_NUMBER() OVER (ORDER BY table.column ASC)) AS row_number
 FROM 
  table

Upvotes: 3

kiks73
kiks73

Reputation: 3758

Yon can take a look to this question and the answer that I report here:

There is no ranking functionality in MySQL. The closest you can get is to use a variable:

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r

In this way you can add a row counter to your result.

Upvotes: 0

Related Questions