Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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