Posted on 7 Comments

magic group numbering in SQL select or update

I came up with the following trick in response to a question in the #mysql channel on Freenode. A user needed to create a unique identifier for multiple otherwise duplicate entries. Yes that was bad schema design to begin with, but it was a fun challenge to see if it could be resolved without scripting. And it can… it’s based on a known trick of numbering output rows. What’s new is restarting the counter for each group (name).

CREATE TABLE number (name CHAR(10), val INT DEFAULT 0);
INSERT INTO number (name)
     VALUES ('foo'),('bar'),('foo'),('foo'),('bar');

SET @lastval=0, @lastuser='';

UPDATE number
   SET val=(@lastval:=IF(name=@lastuser,@lastval+1,1)),
       name=(@lastuser:=name)
 ORDER BY name;

SELECT * FROM number ORDER BY name,val;
+------+------+
| name | val  |
+------+------+
| bar  |    1 | 
| bar  |    2 | 
| foo  |    1 | 
| foo  |    2 | 
| foo  |    3 | 
+------+------+
Posted on 7 Comments

7 thoughts on “magic group numbering in SQL select or update

  1. You mean like this?

    http://www.xaprb.com/blog/2007/01/11/how-to-implement-a-queue-in-sql/

    it is pretty neat though. yours works for an unlimited queue length, Baron’s is a bit more for a queue that has a certain length.

  2. I think adding auto incremented id column and adding it to primary key is better option.

    ALTER TABLE number ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (name, id)

  3. Yep, but did you know that that special form of auto-inc (as part of a composite key) actually only works with MyISAM.
    The original question was about a quick fixup for an existing situation and the number would actually be appended to the name…

  4. Any idea how to do the same with Oracle SQLplus?

    Thanks!
    Adam

  5. Nop, sorry. Not an Oracle expert…

  6. This looks great. Any idea how to do this in SQL Server?

  7. not an MSSQL expert either 😉

Comments are closed.