CODEWITHSUNDEEP
mysqlsql
Bisoux
Bisoux

Reputation: 522

Add incremental id to mysql result

My data looks like the following,

Data
A
A
B
B
C
X
D

I want to extract the unique/distinct results with an incremental value. It should look like the following,

ID  VALUE
1   A
2   B
3   C
4   D
5   X

I have tried the below,

SET @row := 0;
SELECT @row := @row + 1 , mytable.mycolumn as VALUE
FROM change mytable, (SELECT @row := 0) r;

The above gives me the result as,

    VALUE
1   A
2   A
3   B
4   B
5   C
6   D
7   X

Upvotes: 0

Views: 63

Answers (3)

Philip Petrov
Philip Petrov

Reputation: 970

I will start from your attempt. The issue is that you have repeated values. You must reduce them first. To get all unique values ordered alphabetically, you must do this:

SELECT DISTINCT mycolumn
FROM mytable
ORDER BY mycolumn;

Now in your query, replace mytable with the above query as a nested select in the FROM:

SELECT @row := @row + 1 AS ID, mytable_distinct.mycolumn as VALUE
FROM (  SELECT DISTINCT mycolumn
        FROM mytable
        ORDER BY mycolumn  ) AS mytable_distinct, (SELECT @row := 0) r;

A simpler and cleaner solution is to use the ROW_NUMBER function in newer MySQL/MariaDB versions. You can do it like this:

SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS ID, mycolumn AS VALUE
FROM (  SELECT DISTINCT mycolumn
        FROM mytable ) AS mytable_distinct;

Note that we moved the ORDER BY outside of the nested query because the ROW_NUMBER() function expects ORDER BY operator anyway (no need to do this work twice).

There is an even simpler way to do the removing of duplicates without nested queries - it's by using the GROUP BY operator. The above query can be rewritten like that:

SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS ID, mycolumn AS VALUE
FROM mytable
GROUP BY mycolumn;

Despite the cleaner code, I wouldn't use GROUP BY because while it will technically produce the same result and probably the DBMS is smart enough to parse it the same way as SELECT DISTINCT, it is meant for different purpose (for aggregating data).

Upvotes: 1

Ketan Vekariya
Ketan Vekariya

Reputation: 352

For example your table name is mytable then use below query else just replace only mytable with your table name

SELECT @rownum := @rownum + 1 AS row_number,
       table1.data
FROM   (SELECT DISTINCT data
        FROM   mytable) table1
       JOIN (SELECT @rownum := 0) r

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

If you are using MySQL 8+, then ROW_NUMBER is the way to go here:

SELECT ROW_NUMBER() OVER (ORDER BY Data) AS ID, Data AS VALUE
FROM (SELECT DISTINCT Data FROM mytable) t
ORDER BY ID;

On earlier versions of MySQL, you may remedy your current attempt by generating the row number with user variables after first selecting distinct data values:

SET @row := 0;
SELECT @row := @row + 1 , Data as VALUE
FROM (SELECT DISTINCT Data FROM mytable) t, (SELECT @row := 0) r;

This works, as the demo below shows.

Demo

Upvotes: 1

Related Questions