Solved

# Mysql  counting + 1, as to increment a record

Posted on 2010-04-06
Medium Priority
388 Views
I have 2 feilds

select name, phonenumber from phonebook
I want to increment each record to return

name    Phonenumber     count
jay          4021568             1
pete         2588425            2
phil          1547454            3
john         1256654            4

i want to do something like

select name, phonenumber, count(phonenumber)+1 from phonebook.
Obviously this is incorrect, i just need the syntax to assign and increment a row number

Thankyou
0
Question by:kingjely
• 4
• 2

LVL 143

Expert Comment

ID: 29906120
yes, this is possible:
``````et @i = 0;
select id, @i:=@i+1 as myrow from mytable
``````
0

LVL 6

Accepted Solution

tikusbalap earned 1500 total points
ID: 29909271
Try this:

SELECT name, phonenumber, @num := @num + 1 count  from phonebook, (SELECT @num := 0) d;
0

LVL 8

Author Comment

ID: 29967946

select custcode, id, @i:=@i+1 as counter from customers
limit 4

Custcode         Id        Counter
123                  2           null
456                  55         null
789                  6           null
159                  54         null
0

LVL 8

Author Comment

ID: 29969123
HI Tikus,

your example works!  great, could you explain what each line does that i have pointed out, so i understand what you have done, does it have to be in this order ect?

SELECT name, phonenumber,
@num := @num + 1 count         <======================= plz explain what this does
from phonebook,
(SELECT @num := 0) d;            <======================= plz explain what this does.
0

LVL 8

Author Comment

ID: 29969166
What does the 'd'    do?

thankyou
0

LVL 8

Author Closing Comment

ID: 31711311
So

SELECT name, phonenumber,
@num := @num + 1 count         <======================= this is the counter 0+1
from phonebook,
(SELECT @num := 0) d;            <======================= This select sets the counter to '0'

and the d at the end is giving the table an alias.
0

LVL 6

Expert Comment

ID: 29989089
(SELECT @num := 0) d. It will create new view (table) with record variable @num. "d" is alias.

SELECT name, phonenumber, @num := @num + 1 count  from phonebook, (SELECT @num := 0) d.
It will select from table "phonebook" and table "d" as i explained above. Since table "d" was consist variable @num, select @num := @num + 1 will display counter and update @num in table "d" respectively.

Hope helps.

NB: Should I got rank A? :)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
###### Suggested Courses
Course of the Month4 days, 15 hours left to enroll