On 24-Mar-2003 Trevor Smith wrote:
> I want to create a table of customers on a delivery route. I want to
> include the order of delivery in the table. Creating a column with a
> sequential id # would be very cumbersome because in the future some new
> customers may subscribe -- and these new customers may need to be
> inserted into the delivery order. Therefore a column with sequential id
>#s would need to be shifted to make space for the new rows. Very
> inelegant.
>
> The only idea I can come up with is to create a "next customer" column
> and use a programmng language to use that column to view the table as a
> linked list. This is also not what I prefer.
>
> I'm looking for a simple, mysql-only solution that can store the
> delivery order in the table and that can be updated easily.
>
> Any suggestions?
>
It's probably best to use a del_order column, but if you're sold
on the linked list, user variables could do this (not reccommended).
mysql> select * from b;
+----+-------+-------+
| id | name | ncust |
+----+-------+-------+
| 1 | one | 2 |
| 2 | two | 4 |
| 3 | foo | 5 |
| 4 | bar | 3 |
| 5 | baz | 6 |
| 6 | phsst | 7 |
| 7 | bogon | 0 |
+----+-------+-------+
7 rows in set (0.01 sec)
mysql> set @nc=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select id,name, (@nc:=ncust) as next from b where [EMAIL PROTECTED] LIMIT 1;
+----+------+------+
| id | name | next |
+----+------+------+
| 1 | one | 2 |
+----+------+------+
1 row in set (0.00 sec)
mysql> select id,name, (@nc:=ncust) as next from b where [EMAIL PROTECTED] LIMIT 1;
+----+------+------+
| 2 | two | 4 |
+----+------+------+
mysql> select id,name, (@nc:=ncust) as next from b where [EMAIL PROTECTED] LIMIT 1;
+----+------+------+
| 4 | bar | 3 |
+----+------+------+
mysql> select id,name, (@nc:=ncust) as next from b where [EMAIL PROTECTED] LIMIT 1;
+----+------+------+
| 3 | foo | 5 |
+----+------+------+
mysql> select id,name, (@nc:=ncust) as next from b where [EMAIL PROTECTED] LIMIT 1;
+----+------+------+
| 5 | baz | 6 |
+----+------+------+
mysql> select id,name, (@nc:=ncust) as next from b where [EMAIL PROTECTED] LIMIT 1;
+----+-------+------+
| 6 | phsst | 7 |
+----+-------+------+
mysql> select id,name, (@nc:=ncust) as next from b where [EMAIL PROTECTED] LIMIT 1;
+----+-------+------+
| 7 | bogon | 0 |
+----+-------+------+
mysql> select id,name, (@nc:=ncust) as next from b where [EMAIL PROTECTED] LIMIT 1;
Empty set (0.00 sec)
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
