CODEWITHSUNDEEP
mysqlsql
user10398682
user10398682

Reputation:

how to number a resultset from MySQL (another option to not use user variables)

For example a create this query to get all the users and their posts asociated

SELECT users.nameUser, posts.namePost
FROM users
JOIN posts ON users.id = posts.user_id;

It works fine, but I need to number the results; so I declared a user variable in this way

SET @counter = 0;

Now I use it

SELECT (@counter := @counter + 1) AS NP, users.nameUser, posts.namePost
FROM users
JOIN posts ON users.id = posts.user_id;

But now with MySQL 8 and window functions I need to use another way and avoid user variables

How I can achieve this?

Remember...

If you need your variable increments its value you must to use the := sintax otherwise it keeps the same value no matter the size of the resultset

Upvotes: 2

Views: 36

Answers (1)

user10398682
user10398682

Reputation:

Since MySQL 8 supports Window Functions, you can use: row_number()

Whats does row_number() do?

  1. It starts a counting since 1 and increments it on one plus one
  2. row_number() needs to work with OVER clausule to make an asc o desc order over an specific column (in this case nameUser)

My code should looks like this

SELECT row_number() OVER(ORDER BY users.nameUser) AS NP, users.nameUser, posts.namePost
FROM users
JOIN posts ON users.id = posts.user_id;

Upvotes: 1

Related Questions