Menu
Here is my MySQL query:
SELECT name FROM table; How can I also select an increment counter alongside name? Expected output:
Jay 1 roy 2 ravi 3 ram 4 
888
To add an additional counter to your MySQL query result set, you need to create a variable using the SET statement first. Then, you need to increment the variable in your SELECT statement so that the variable value goes up for each row you have in the result set.
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 COUNT() Function The COUNT() function returns the number of records returned by a select query.
You can still use an identity column without changing your other tables. Something like CREATE TABLE counter (counter INT IDENTITY(1, 1) PRIMARY KEY ); , and then INSERT INTO counter DEFAULT VALUES; SELECT @@SCOPE_IDENTITY; DELETE FROM counter WHERE counter = @@SCOPE_IDENTITY; , perhaps?
select name, @rownum := @rownum + 1 as row_number from your_table cross join (select @rownum := 0) r order by name This part:
cross join (select @rownum := 0) r makes it possible to introduce a variable without the need of a seperate query. So the first query could also be broken down into two queries like this:
set @rownum := 0; select name, @rownum := @rownum + 1 as row_number from your_table order by name; for instance when used in a stored procedure.
160
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
