2

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.

8

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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.