MySQL Seeding ORDER BY, set absolute position in results
Posted: Wed May 13, 2009 11:29 am
Hi everyone
I'm trying to get a SELECT query ordered in a pretty specific way, but I've no idea how to do it.
My table has 2 fields, id and position
I'm trying to return the results ordered by id, but if position is not null then that record goes into the results at that particular position.
Here's my test DB:
So given that dataset, this is the result I'm hoping to get:
So the value of the position field dictates the absolute position in the results.
Does anyone know how to do this?
Thanks, B
I'm trying to get a SELECT query ordered in a pretty specific way, but I've no idea how to do it.
My table has 2 fields, id and position
I'm trying to return the results ordered by id, but if position is not null then that record goes into the results at that particular position.
Here's my test DB:
Code: Select all
CREATE TABLE IF NOT EXISTS `test` ( `id` INT(11) NOT NULL DEFAULT '0', `POSITION` INT(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO `test` (`id`, `POSITION`) VALUES(101224, NULL);INSERT INTO `test` (`id`, `POSITION`) VALUES(546457, NULL);INSERT INTO `test` (`id`, `POSITION`) VALUES(89212, 2);INSERT INTO `test` (`id`, `POSITION`) VALUES(98724, NULL);INSERT INTO `test` (`id`, `POSITION`) VALUES(54666, 8 );INSERT INTO `test` (`id`, `POSITION`) VALUES(54678, NULL);INSERT INTO `test` (`id`, `POSITION`) VALUES(65462, NULL);INSERT INTO `test` (`id`, `POSITION`) VALUES(87924, 1);INSERT INTO `test` (`id`, `POSITION`) VALUES(78920, NULL);INSERT INTO `test` (`id`, `POSITION`) VALUES(3317, NULL);Code: Select all
id position
87924 1
89212 2
3317 NULL
54678 NULL
65462 NULL
78920 NULL
98724 NULL
54666 8
101224 NULL
546457 NULLDoes anyone know how to do this?
Thanks, B