?
Solved

Mysql  counting + 1, as to increment a record

Posted on 2010-04-06
7
Medium Priority
?
388 Views
Last Modified: 2012-08-14
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
Comment
Question by:kingjely
  • 4
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 29906120
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
 
LVL 6

Accepted Solution

by:
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

by:kingjely
ID: 29967946
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 8

Author Comment

by:kingjely
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

by:kingjely
ID: 29969166
What does the 'd'    do?

thankyou
0
 
LVL 8

Author Closing Comment

by:kingjely
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

by:tikusbalap
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question