insert in an ordered set, then reorder
Hi there,
A question on how to insert in an ordered set without repeating and then reorder the table.
CREATE TABLE colors (
id int not null AUTO_INCREMENT,
color varchar(50) not null,
weight int not null unique
)
INSERT INTO colors (color, weight) VALUES (blue, 2)
INSERT INTO colors (color, weight) VALUES (red, 3)
INSERT INTO colors (color, weight) VALUES (black, 1)
INSERT INTO colors (color, weight) VALUES (white, 4)
Hot to insert/update (orange, 2) before red or after blue? and reorder the set?
And the best way to insert at beginning/end also with reordering?
by the end I need to display an ordered list like this.
SELECT * FROM colors ORDER BY weight DESC
regards,