MySQL: Show row number in SELECT query output

Question

I have a table in a MySQL database. I want to display the item number (not the primary key) and then a row number.

item_num, row_number
1, 1
12, 2
1234, 3
15, 4
29, 5

Here is my query:

select item_num, row_number() over(partition by item_num) from items;

My output is this:

item_num, row_number
1, 1
12, 1
1234, 1
15, 1
29, 1

How can I fix this issue and show the correct row number for each item?

you have 1 unique value per partition, so naturally you have 1 per row. Is id a string or integer? Remove the partition and add an order by — Stu
– Stu, Commented Jun 21, 2022 at 21:30
@Stu, they are just values, it is not a primary key in my table. let me change the column name to avoid confusion. — Learner
– Learner, Commented Jun 21, 2022 at 21:51

Talia Smiley · Accepted Answer · 2022-06-21 23:19:24Z

2

Answer

The following query should create the behaviour you want:

SELECT item_num, ROW_NUMBER() OVER() FROM items;

Explanation

The ROW_NUMBER() formula sequentially numbers the rows of each partition which is passed into it. It begins again at 1 for each partition, independently. Therefore, attempting to partition the database by item_num before using ROW_NUMBER() will result in each unique item_num being assigned a row number of 1.

Instead, use a blank OVER() clause. There's no need to partition the database or order it in any specific way, since you simply want to apply ROW_NUMBER() based on the order already present in the database.

edited Jun 21, 2022 at 23:19

answered Jun 21, 2022 at 22:34

Talia Smiley

1,1817 silver badges18 bronze badges

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

4 Comments

ysth Over a year ago

you can just do OVER (); no need for a dummy ORDER BY. dbfiddle.uk/…

Talia Smiley Over a year ago

@ysth Although I don't have the opportunity to test it right now, my understanding is that ROW_NUMBER() requires the associated OVER() to have either an ORDER BY or PARTITION BY clause, and will throw an error if neither are present. Is this not the case?

ysth Over a year ago

that is not the case; see the fiddle I linked. the doc says "ORDER BY affects the order in which rows are numbered. Without ORDER BY, row numbering is nondeterministic." so you could do a query twice and get different orders between them, and there's not necessarily any relation between the order ROW_NUMBER returns and the order the query actually returns (though in practice it would surprise me if they differed). But that's all just as true for your ORDER BY NULL.

Talia Smiley Over a year ago

Ah, I see. I didn't even notice your link before now. I'll update my answer to reflect this.

Collectives™ on Stack Overflow

MySQL: Show row number in SELECT query output

1 Answer 1

Answer

Explanation

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Answer

Explanation

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related