Menu
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
+------+---------------+-----------------+----------------+
230
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
UUID always occupies 16 bytes. For Auto Increment Integer, when stored as Long format, it occupies 8 bytes. If the table itself has only a few columns, the extra primary key space overhead will become more significant.
To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment. Inserting records into the table. To display all the records.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
