Menu
I have a table named table1 in SQL server as follows:
colA
-------
A123
Z123
C123
B123
now I want to use one SQL statement to get the result as follows:
ID colA
--------
1 A123
2 Z123
3 C123
4 B123
The order of colA is as the order of row in the table. Do not need to sort it.
how to do that?? Thanks a lot
644
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT.
No. If you really need this, you will have to generate ID manually.
By default, the AUTO_INCREMENT starts with 1 and increases by 1. Example: We will create Students table with fields Student_ID, First_Name, Last_Name, we will auto generate Student_ID by using auto increment and will make it Primary Key for the table.
Here's what I always do when I need an incrementing ID without sorting the data (because my other row or rows are neither alphabetical nor chronological). Also, I despise using temp tables unless they are absolutely necessary.
SELECT ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) as ID, colA
FROM table1
Example using ROW_NUMBER
SELECT ROW_NUMBER()
OVER (ORDER BY colA) AS Row,
colA
FROM table1
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
