Number records of a query in MySQL (alternative to variables defined by the user)

Question

Number records of a query in MySQL (alternative to variables defined by the user)

Navigation

#1 by (3 votes)

4

Inside the MySQL database manager I have a table of which I do a% general% co_ and I want to list with an incremental number all records, I can do it originally with a user defined variable as follows :

SET @counter = 0;

SELECT (@counter := @counter + 1) AS NP, name, email FROM profiles;

It is important to mention that for the variable SELECT to go increasing the value you need the syntax counter otherwise only the := symbol is used regardless of how many records exist it will keep the same declared value at the beginning.

The result I get is the following

+------+--------------+------------------+
| NP   | name         | email            |
+------+--------------+------------------+
|    1 | dfsdfsf      | dsfdsfsf         |
|    2 | alfa         | [email protected]    |
|    3 | danielo      | [email protected]    |
|    4 | ortega   oga | [email protected]    |
|    5 | diana        | [email protected]    |
|    6 | elena        | [email protected]    |
|    7 | gonzalo      | [email protected]    |
|    8 | jorge        | [email protected]    |
|    9 | mendez       | [email protected]    |
|   10 | pedro        | [email protected]    |
|   11 | arnoldo      | [email protected]    |
+------+--------------+------------------+

However I do not want to depend on this option since I need to know how to do it with a = to avoid being declared variables

mysql sql

asked by element 20.09.2018 в 17:36

source

1 answer

addEventListener fails to click the first time Show array list in Vuejs

score 3 · Accepted Answer

Within the features of MySQL 8 (in previous versions there is no possibility of using Window Functions) we can find the window functions , as a specific case:

row_number () which will help me generate sequentially and incrementally a numeric record starting from 1 and increasing by 1

The previous code could then be rewritten as follows

SELECT row_number() OVER (ORDER BY name) AS NP, name, email
FROM profiles;

The result would be as follows

+------+--------------+------------------+
| NP   | name         | email            |
+------+--------------+------------------+
|    1 | dfsdfsf      | dsfdsfsf         |
|    2 | alfa         | [email protected]    |
|    3 | danielo      | [email protected]    |
|    4 | ortega   oga | [email protected]    |
|    5 | diana        | [email protected]    |
|    6 | elena        | [email protected]    |
|    7 | gonzalo      | [email protected]    |
|    8 | jorge        | [email protected]    |
|    9 | mendez       | [email protected]    |
|   10 | pedro        | [email protected]    |
|   11 | arnoldo      | [email protected]    |
+------+--------------+------------------+

Observations:

I do not depend on the creation of a session variable

row_number() need to work with the statement OVER so that in this way it is indicated on which column you want to do   the order, in this case we work on the column name

We can also add an alias to row_number() to improve its reading

Sorting that OVER is of type ascendente by default unless otherwise indicated