MYSQL: adding unique numeric value to table based on unique row value?

Question

I have this table and I want to split it in three tables so i have names, a relation table between names and orders, and the third which contains just orders. so i think a simple way could be to add a unique number column here

+----+------+-----------------------+-------+--------------+
| id | name | address               | phone | order_number |
+----+------+-----------------------+-------+--------------+
|  1 | Joe  | Joes Address          | 1111  | 1390842      |
|  2 | Paul | Pauls Address         | 2222  | 9082309      |
|  3 | Greg | Gregs Address         | 3333  | 0928340      |
|  4 | Lucy | Lucys Address         | 4444  | 9028340      |
|  5 | Paul | Pauls Address         | 2222  | 8958399      |
|  6 | Tom  | Toms Address          | 5555  | 9084024      |
|  7 | Lucy | Lucys Another Address | 4444  | 9801983      |
|  8 | Paul | Pauls Another Address | 2222  | 0982304      |
+----+------+-----------------------+-------+--------------+

and i want to add a numeric column with a incremental number associated with the unique name value so that the expected result is

+----+------+-----------------------+-------+--------------+---+
| id | name | address               | phone | order_number |NID|
+----+------+-----------------------+-------+--------------+---|
|  1 | Joe  | Joes Address          | 1111  | 1390842      | 1 |
|  2 | Paul | Pauls Address         | 2222  | 9082309      | 2 |
|  3 | Greg | Gregs Address         | 3333  | 0928340      | 3 |
|  4 | Lucy | Lucys Address         | 4444  | 9028340      | 4 |
|  5 | Paul | Pauls Address         | 2222  | 8958399      | 2 |
|  6 | Tom  | Toms Address          | 5555  | 9084024      | 5 |
|  7 | Lucy | Lucys Another Address | 4444  | 9801983      | 4 |
|  8 | Paul | Pauls Another Address | 2222  | 0982304      | 2 |
+----+------+-----------------------+-------+--------------+---+

how can I do it??

Do you just want to display NID as query result or want to add the column in table too? — Harshil Doshi
– Harshil Doshi, Commented Oct 14, 2017 at 20:06

M Khalid Junaid · Accepted Answer · 2017-10-14 20:10:11Z

1

Some what similar to your desired result set by using user defined variables

select `id`, `name`, `address`, `phone`, `order_number`,
@b:= case when `name` <> @a then @b + 1 else @b end NID,
@a:= `name`
from (
    select *
    from demo b,
    (select @a:=null,@b:=1) a
    order by name 
) c

DEMO

Another simple version by assuming that id column is set to auto-increment if so then you can use correlated sub query to pick the minimum id for same name records

select a.*,
(select min(id) from demo b where a.name = b.name) nid
from demo a

Note above will not guarantee the sequence it will totally depend on id column value

DEMO

edited Oct 14, 2017 at 20:10

answered Oct 14, 2017 at 19:58

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

Comments

Harshil Doshi · Accepted Answer · 2017-10-15 17:56:29Z

0

If you just want to display NIDs, then @M Khalid Junaid's answer will works fine.

But if you want to add the Column NID in the table, then following query will get the job done:

alter table t
add column nid integer;

update t
set nid = (Select 
           (case when count(name)>1 then min(id)
                 else  id
            end)
           from
           (select *from t) x
           where t.name = x.name          
           );

Note: The Nid doesn't contains incremental sequence. It is based on the id column.

Hope it helps!

answered Oct 15, 2017 at 17:56

Harshil Doshi

3,6123 gold badges18 silver badges41 bronze badges

Collectives™ on Stack Overflow

MYSQL: adding unique numeric value to table based on unique row value?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related