Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

Mysql find and incrementally number matching rows?

I have a table, 'answers' and a field on that table 'q_id', and another field 'num' (nothing is in 'num' right now).

Is there a way to run a query that will find all rows with matching 'q_id' and number them by putting 1 or 2 or 3 or 4 or 5, ... in the 'num' field?  That way I can identify them even though the q_id is the same.

If possible, can you put it together for me please?

Thank you,  Chris

0
St_Aug_Beach_Bum
Asked:
St_Aug_Beach_Bum
1 Solution
 
xeonolCommented:
try
set @tmpid =-1; set @tmp = 1; update answers set num = (select @tmp := if(@tmpid<>q_id, 1, @tmp + 1)), q_id=(select @tmpid := q_id) order by q_id;
0
 
Ray PaseurCommented:
Good news - "num" does not appear to be a reserved word.  Still it's not very descriptive so you might want to choose a word with more meaning.
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

This is pidgin code, but hopefully the design pattern will be clear.  It depends on something you did not mention - namely a UNIQUE field (perhaps an autoincrement key).  If you don't already have one of these, use ALTER TABLE to add one.
// PIDGIN CODE
error_reporting(E_ALL);


$old = mysql_query("SELECT q_id, unique_id FROM answers ORDER BY q_id");
$old_qid = '?';

while ($row = mysql_fetch_assoc($old))
{
    if ($row["q_id"] != $old_qid)
    {
        $old_qid = $row["q_id"];
        $num = 1;
    }
    $uql = mysql_query("UPDATE answers SET num = $num WHERE unique_id = '{$row["unique_id"]}' LIMIT 1");
    $num++;
}

Open in new window

0
 
St_Aug_Beach_BumAuthor Commented:
oh, hmmm, I do have a unique field, time_stamp, from the time the questions came in. I can even order by that.

Unfortunitly - I've got to be somewhere in about 20 minutes, so I can't try these solutions out right now. I will later this afternoon though :)

Chris
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Ray PaseurCommented:
OK - just make sure that the time_stamp column is immutable - once set, it stays set forever and that it is unique - no collisions whatsoever.  Then if those two conditions are true, you can use it for the unique id
0
 
FugasCommented:
You should better add an autoincrement field and base your updates on this fields. It will be more efficient when you have more records in your table.

In oracle there you can select the unique rowid, but this option is not implemented in mysql.
0
 
St_Aug_Beach_BumAuthor Commented:
Thank you xeonol, Ray and Fugas.

Xeonol's solution... I was a little hesitant on that one, looked - to - easy... but I ran it first since he posted the first solution and it ran right, with each matching row being numbered correctly, and on the first try!

Thank you!

0
 
St_Aug_Beach_BumAuthor Commented:
And thank you Fugas and Ray for the extra tips on the use of 'num' and incrementing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now