1

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.

Alvin SIU
  • 1,022
  • 10
  • 28
  • What would that sequence be used for? You can't use it for any query, so what's the point of it? – Mjh Feb 03 '17 at 09:46
  • 1
    Possible duplicate of [ROW\_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) – kiks73 Feb 03 '17 at 09:47
  • @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 Feb 03 '17 at 09: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 Feb 03 '17 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](http://stackoverflow.com/questions/1964811/row-rank-in-a-mysql-view) – Panagiotis Kanavos Feb 03 '17 at 10:34
  • @PanagiotisKanavos it looks like you're missing something here - first off, it's completely pointless to drive your DB to a grinding halt when you want a row with rownumber = 1 000 000. We have primary keys and other various identifiers for a reason. If you prefer to do it the bad way - great, it's you and your code. I see 0 reasons why you're debating whether it's "useful" or not when it's **not needed at all** - there are better ways, we all know them, many people even mentioned them. Curing someone's OCD in desire for sequential identifiers leads to evil things. – Mjh Feb 03 '17 at 10:41
  • @Mjh perhaps you missed the point where the OP where the OP explains that the end users want to see the results with a row number starting from 1. Anyway, you can explain this in an answer if you are certain. Generating and using number sequences is one of the most interesting puzzles in SQL, going back all the way to Celko's "SQL for smarties" - a book recommended for any DBA and developer. There are various techniques, some of them faster than others. – Panagiotis Kanavos Feb 03 '17 at 10:49
  • @PanagiotisKanavos so you, a person with ~32k reputation are advocating for doing this on database level, stating it's useful (we determined it isn't), generating a useless piece of information for something that can be done on the level of language used to display this information (in a simple loop). I bet you that OP didn't explain everything, and he'll try to use this info to read the row or delete it (eventually) and once the db grows - we'll be back here discussing how MySQL's slow. It's simply responsible to *predict* and *warn* instead of blindly *assure* someone of something. – Mjh Feb 03 '17 at 10:54
  • @Mjh no, me with 20 years working on large databases and data warehouses is saying to you not to get stuck, not to assume everyone else is ignorant and actually consider the question. That's a *recurring* puzzle with many different clever solutions. Yes, you can generate the order on the client side. There are uses for doing so on the server side too. Once you know what is possible *outside* a single narrow product-centric view, you can discover many possibilities – Panagiotis Kanavos Feb 03 '17 at 10:57
  • @Mjh and btw 1M is small data these days. Hardly worth partitioning. – Panagiotis Kanavos Feb 03 '17 at 10:58
  • @PanagiotisKanavos - I don't recall anyone asking for you to verify how long you're working with databases (my experience is larger than yours for 5 years). This is a simple matter of predicting what the person in question is after. You think it's fine to do this on database side, I think it's not and I mentioned why. We're turning this into a meaningless discussion filled with ego, so I'll be the first one to stop. Sequence generated via SQL is useless in this case, it bears no meaning and it can't be used for anything *efficient*. I don't think we need to debate whether I'm correct. – Mjh Feb 03 '17 at 11:12

2 Answers2

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
Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
  • 1
    Another possibility is the second answer [here](http://stackoverflow.com/questions/6168352/simulate-row-number-using-numbers-table). 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 – Panagiotis Kanavos Feb 03 '17 at 11:00
  • 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 – Alvin SIU Apr 27 '17 at 09:39
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.

Community
  • 1
  • 1
kiks73
  • 3,718
  • 3
  • 25
  • 52