generate auto increment value from unique id mysql

Question

I collect the same similar data with different type value based on id in one table:

 +------+---------------+-----------------+----------------+ 
 |  id  |   TransID     |     Amount      |   InsertDate   |
 +------+---------------+-----------------+----------------+ 
 |  1   |      1        |       12        |   19-03-2004   |
 |  2   |      2        |       9         |   20-03-2004   |
 |  3   |      3        |       4         |   21-03-2004   |
 |  4   |      1        |       12        |   22-03-2004   |
 |  5   |      2        |       9         |   23-03-2004   |
 |  6   |      3        |       4         |   24-03-2004   |
 |  7   |      1        |       12        |   25-03-2004   |
 +------+---------------+-----------------+----------------+

When I select the table based on the TransID of 1, I want to have a unique auto increment ID for the record based on the id of the table.

How to do this? So the result would be

 +------+---------------+-----------------+----------------+--------------- 
 |  id  |   TransID     |     Amount      |   InsertDate   | NewGeneratedID
 +------+---------------+-----------------+----------------+----------------- 
 |  1   |      1        |       12        |   19-03-2004   |       1
 |  4   |      1        |       12        |   22-03-2004   |       2
 |  7   |      1        |       12        |   25-03-2004   |       3
 +------+---------------+-----------------+----------------+ ---------------

AND when I select to only a specific id of the table, for example the id of 4, it will give me the NewGeneratedID of 2, not 1.

 +------+---------------+-----------------+----------------+--------------- 
 |  4   |      1        |       12        |   22-03-2004   |       2
 +------+---------------+-----------------+----------------+

I need to identify it to client side while merging all type with different identity of name. — Al Kasih, Apr 20 '16 at 19:25
This look like something strange to do... Is this your actual problem? or some odd [workaround to other problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem)? — It-Z, Apr 29 '16 at 09:12
I don't know how to do it in mySQL, but in SQL Server there is a row_number() function when used in conjunction with the partition you can definitely solve this problem.. This answer might get you half-way there http://stackoverflow.com/questions/19589707/how-to-get-rownumber-with-partition-in-mysql — ewahner, Apr 29 '16 at 19:03

Fathah Rehman P · Answer 1 · 2016-04-19T17:58:43.493

8

You can use following query for your requirement

    SELECT t.id,t.TransID ,t.Amount,t.InsertDate ,(@num:=@num+1) AS
 NewGeneratedID  FROM table1 t cross join (SELECT @num:=0) AS dummy 
where t.TransID=1  ORDER BY id;

edited Apr 19 '16 at 17:58

answered Apr 19 '16 at 17:52

Fathah Rehman P

8,401
4
40
42

Many thanks, Fathah. Can you please explain to me what is cnt and dummy there? – Al Kasih Apr 19 '16 at 17:55
This will always give me 1 for the NewGeneratedID when I select it only one record from table. – Al Kasih Apr 19 '16 at 18:00
@klaudia - if you select only one record, then its auto generated id will be always 1 right – Fathah Rehman P Apr 19 '16 at 18:01
I want it to count it before it gives me the result, so that when I select the ID of 4 from the table, it will give me the NewGeneratedID of 2 – Al Kasih Apr 19 '16 at 18:02
I think its better to do that in query processing part. – Fathah Rehman P Apr 19 '16 at 18:12
Yes, but we have already have many records in the table, and we are looking for the fastest way for this. – Al Kasih Apr 19 '16 at 18:13
@klaudia - i think something is missing in your processing – Fathah Rehman P Apr 19 '16 at 18:18

JCalcines · Answer 2 · 2016-05-03T11:47:43.363

8

I have to warn you that the next query is inefficient but it can achieve what you need.

SELECT t.id, t.TransID ,t.Amount, t.InsertDate, 
    (SELECT COUNT(id) FROM table1 AS aux 
     WHERE t.TransID = aux.TransID and aux.id <= t.id)
FROM table1 t 
WHERE t.TransID = 1  ORDER BY id;

If the process where you need it is critical on time you shouldn't use this query. However if you are only interested in getting one record you better use the following query which is efficient enough.

SELECT t.id, t.TransID , t.Amount, t.InsertDate, COUNT(*)
FROM table1 t inner join table1 aux where t.TransID = aux.TransID
WHERE aux.id <= t.id and t.id = 4
GROUP BY t.id, t.TransID , t.Amount, t.InsertDate;

UPDATE: The restriction aux.id <= t.id stablishes an order among the elements by counting how many elements with lesser id exists. for example the row with id 4 has one row with lesser id (1) for the same transaction and the row with id 7 has two rows with lesser id (1 and 4)

edited May 03 '16 at 11:47

answered Apr 19 '16 at 22:14

JCalcines

1,236
12
25

Why does `aux.id < t.id` ? – Al Kasih Apr 22 '16 at 20:30
1

`and` is missing here: aux.id < t.id `and` t.id = 4 – Saic Siquot Apr 28 '16 at 22:19
@klaudia I apologize because I couldn't answer you before. That clause makes and order between the elements. I will update the answer to explain it. – JCalcines May 03 '16 at 11:34
Revising my answer I realised that if I change `<` for '<=' I don't have to add 1 to the count. I already have updated my answer. – JCalcines May 03 '16 at 11:50

rev_dihazum · Answer 3 · 2016-04-30T02:58:12.830

5

If you are planing to add an column for that you could use MySQL INSERT TRIGGER something like bellow:

CREATE TRIGGER INSERT_Trigger BEFORE INSERT ON Your_Table
FOR EACH ROW BEGIN
  SET NEW.NewGeneratedID = (SELECT COUNT(*)+1 FROM Your_Table WHERE TransID = NEW.TransID);
END

EDIT: Strategy is something like that, If you are using a physical column for NewGeneratedID then for each Insertion in your table count how many ROWs already exists for TransID(of new row) and set NewGeneratedID(of new row) by count + 1 . Remember, if you need to DELETE from table then there must need a AFTER DELETE trigger to make consistent NewGeneratedID.

edited Apr 30 '16 at 02:58

answered Apr 19 '16 at 18:25

rev_dihazum

818
1
9
19

Can you please help me to understand this process, or implement with the complete query to achieve this. I never use Create TRIGGER before. – Al Kasih Apr 19 '16 at 18:27
@klaudia modified answer. Plz see that and feedback me. – rev_dihazum Apr 19 '16 at 18:32

score 5 · Answer 4 · answered Apr 28 '16 at 03:13

Use the below query when you are selecting based on TransactionID

SELECT t.id,t.TransID ,t.Amount,t.InsertDate ,(@num:=@num+1) AS
NewGeneratedID  FROM MyTable t 
cross join (SELECT @num:=0) AS dummy  where t.TransID=1  ORDER BY id;

And For the id condition check use the below query

select t1.* from 
(SELECT t.id,t.TransID ,t.Amount,t.InsertDate,
(@num:=@num+1) AS  NewGeneratedID  
FROM MyTable t 
cross join (SELECT @num:=0) AS dummy  
where t.TransID=1  ORDER BY id) t1 
where t1.id=4 ;

score 4 · Answer 5 · answered Apr 29 '16 at 09:50

This is my suggestion for what I understand in your question:

If generated incremental ids for NewGeneratedID will be permanent:

Update the table and insert those newly generated ids
After that, you can now easily select with a given id and NewGeneratedID

If generated new ids for NewGeneratedID are just temporary:

After selecting and generating incremental ids, save them in a temporary table (See CREATE TABLE)
When data are saved in temporary table, you can now select with a given id and NewGeneratedID
Clean or delete data in temporary table after the session

score 0 · Answer 6 · answered Apr 29 '16 at 19:37

0

Isn't the id field already unique enough ? Why don't you use that ? If you absolutely need a new field you can just add one or any number to the id field and generate another value on every insert. i.e. New Field = ID + Constant.

answered Apr 29 '16 at 19:37

antiskidwarpdrive

321
1
2
8

generate auto increment value from unique id mysql

6 Answers6