CODEWITHSUNDEEP
mysqlmysql-workbench
Kim Barcelo
Kim Barcelo

Reputation: 15

MySQL column1 increment by 1 if column2 changes

I need to have a column that increments when column2 changes to sort it properly.

my example table that I want to happen:

+---------+---------+
| Column1 | Column2 |
+---------+---------+
|       1 | dog     |
|       1 | dog     |
|       2 | cat     |
|       2 | cat     |
|       3 | rat     |
+---------+---------+

if anyone can help, thank you.

Upvotes: 1

Views: 61

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

The DENSE_RANK analytic function can handle this requirement without the need for the first column, e.g.

SELECT
    DENSE_RANK() OVER (ORDER BY Column2) Column1,
    Column2
FROM yourTable;

This answer assumes that you are running MySQL 8 or later.

Upvotes: 1

Related Questions