Mysql counting + 1, as to increment a record

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
LVL 8
kingjelyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, this is possible:
http://forums.mysql.com/read.php?20,192721,192729#msg-192729
et @i = 0;
select id, @i:=@i+1 as myrow from mytable

Open in new window

0
tikusbalapCommented:
Try this:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kingjelyAuthor Commented:
Hi angel, with your answer,

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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

kingjelyAuthor Commented:
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
kingjelyAuthor Commented:
What does the 'd'    do?

thankyou
0
kingjelyAuthor Commented:
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
tikusbalapCommented:
(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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.