update column based on the sort order of another query

Question

I'm trying to add arbitrarily ordered records to a database so that they can be sorted by the ui (or when I query the database ). My problem is I already have the list, and I need to add a default sorting based on alphabetical order. I'm thinking I should be able to do this with a subquery or coalesce, but I can't get it quire right. I'm doing this on MySQL so I'm hoping it's possible to do it at the database level.

Answer 1

Let's start with a table, put data in it, add a sort order column

USE test
DROP TABLE IF EXISTS ordered_names;
CREATE TABLE ordered_names
(
    id int not null auto_increment,
    firstname VARCHAR(20),
    lastname VARCHAR(20),
    PRIMARY KEY (id),
    KEY name_ndx (lastname,firstname)
);
INSERT INTO ordered_names (firstname,lastname) VALUES
('pamela','edwards'),('rolando','edwards'),('diamond','edwards');
ALTER TABLE ordered_names ADD COLUMN sort_order INT DEFAULT NULL;
SELECT * FROM ordered_names;

Let's actually run that:

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS ordered_names;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE ordered_names
    -> (
    ->     id int not null auto_increment,
    ->     firstname VARCHAR(20),
    ->     lastname VARCHAR(20),
    ->     PRIMARY KEY (id),
    ->     KEY name_ndx (lastname,firstname)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO ordered_names (firstname,lastname) VALUES
    -> ('pamela','edwards'),('rolando','edwards'),('diamond','edwards');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE ordered_names ADD COLUMN sort_order INT DEFAULT NULL;
Query OK, 3 rows affected (0.17 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ordered_names;
+----+-----------+----------+------------+
| id | firstname | lastname | sort_order |
+----+-----------+----------+------------+
|  1 | pamela    | edwards  |       NULL |
|  2 | rolando   | edwards  |       NULL |
|  3 | diamond   | edwards  |       NULL |
+----+-----------+----------+------------+
3 rows in set (0.00 sec)

mysql>

Next, let's populate the sort order column:

SET @x = 0;
UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
SELECT * FROM ordered_names;

Let's run that:

mysql> SET @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM ordered_names;
+----+-----------+----------+------------+
| id | firstname | lastname | sort_order |
+----+-----------+----------+------------+
|  1 | pamela    | edwards  |          2 |
|  2 | rolando   | edwards  |          3 |
|  3 | diamond   | edwards  |          1 |
+----+-----------+----------+------------+
3 rows in set (0.00 sec)

mysql>

OK, good so far, let's add four(4) more people and reorder:

INSERT INTO ordered_names (firstname,lastname) VALUES
('javonne','washington'),('richard','washington'),
('carlik','washington'),('dominique','edwards');
SET @x = 0;
UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
SELECT * FROM ordered_names;

Let's run that:

mysql> INSERT INTO ordered_names (firstname,lastname) VALUES
    -> ('javonne','washington'),('richard','washington'),
    -> ('carlik','washington'),('dominique','edwards');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SET @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ordered_names SET sort_order = (@x:=@x+1) ORDER BY lastname,firstname;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 7  Changed: 6  Warnings: 0

mysql> SELECT * FROM ordered_names;
+----+-----------+------------+------------+
| id | firstname | lastname   | sort_order |
+----+-----------+------------+------------+
|  1 | pamela    | edwards    |          3 |
|  2 | rolando   | edwards    |          4 |
|  3 | diamond   | edwards    |          1 |
|  4 | javonne   | washington |          6 |
|  5 | richard   | washington |          7 |
|  6 | carlik    | washington |          5 |
|  7 | dominique | edwards    |          2 |
+----+-----------+------------+------------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!