How to generate sequence number in MySQL view?

Question

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.

What would that sequence be used for? You can't use it for any query, so what's the point of it? — Mjh
– Mjh, Commented Feb 3, 2017 at 9:46
@Mjh there are countless ways you can use a sequence or ROW_NUMBER in queries, which is why most databases already have ranking functions — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 3, 2017 at 9:53
@PanagiotisKanavos a sequence generate like this, that doesn't refer to any primary key of any table in the view is absolutely useless. I know you think you're right, but there's such an easy way to verify what I said - use it. Especially on somewhat larger table. I already know the result, but feel free to amuse yourself. Also, not the label of RDBMS in question. — Mjh
– Mjh, Commented Feb 3, 2017 at 10:01
@Mjh I know, I use sequences and row numbers all the time, which is why I say it's extremely useful. Whether it is to rank, page results or generate unique IDs across tables sequences and ROW_NUMBER are extremely useful. And anyway, it's possible but slow even in views — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 3, 2017 at 10:34

Raymond Nijland · Accepted Answer · 2018-06-19 17:09:58Z

3

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

edited Jun 19, 2018 at 17:09

answered Feb 3, 2017 at 10:56

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Panagiotis Kanavos Over a year ago

Another possibility is the second answer here. The left join trick can return the X Ids and their rank, then the IDs can be used to load the entire row. Indexing on ID probably means the speed won't be bad. It's worth benchmarking

Alvin SIU Over a year ago

Thanks Raymond. I used the second answer to implement the view. Just with a small fix on your SQL. Here is my SQL : create view [table]_row_number as select (select count() + 1 from [table] t where t.id < x.id ) as row_number , x.* from [table] x

Community · Accepted Answer · 2017-05-23 12:17:50Z

0

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.

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Feb 3, 2017 at 9:44

kiks73

3,8383 gold badges30 silver badges55 bronze badges

4 Comments

Panagiotis Kanavos Over a year ago

In this case you should mark the question as duplicate

Alvin SIU Over a year ago

But, it seems to me that MySQL 5.6 cannot create a view with subquery. Let me try next Monday

Panagiotis Kanavos Over a year ago

@AlvinSIU there are other techniques as shown here. SELECT t.id, (SELECT COUNT(*) FROM TABLE WHERE id < t.id) +1 AS NUM from t.

miken32 Over a year ago

@kiks73 no need to delete the answer, but flagging is a good habit to get into.

Collectives™ on Stack Overflow

How to generate sequence number in MySQL view?

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related