Page 1 of 1

MySQL Seeding ORDER BY, set absolute position in results

Posted: Wed May 13, 2009 11:29 am
by batfastad
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:

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);
So given that dataset, this is the result I'm hoping to get:

Code: Select all

id      position
87924   1
89212   2
3317    NULL
54678   NULL
65462   NULL
78920   NULL
98724   NULL
54666   8
101224      NULL
546457  NULL
So the value of the position field dictates the absolute position in the results.
Does anyone know how to do this?

Thanks, B

Re: MySQL Seeding ORDER BY, set absolute position in results

Posted: Thu May 14, 2009 5:00 am
by jaoudestudios
I would use CASE, then create a variable in there and order by that.

Re: MySQL Seeding ORDER BY, set absolute position in results

Posted: Fri May 15, 2009 6:26 am
by batfastad
Hi jaoudestudios
Could you do an example for me of what you mean by CASE and a variable?

Also does this have anything to do with stored procedures? I've not used those yet, but I'd like to try and keep this as a regular query... if that's even possible :?:

Re: MySQL Seeding ORDER BY, set absolute position in results

Posted: Fri May 15, 2009 6:37 am
by jaoudestudios
Its not a stored procedure. I thought of that, however it should be possible without.

Here is a complicated example from years ago (includes php&mysql so a bit confusing)...
http://www.forum.jaoudestudios.com/view ... ?f=13&t=12

This is the part i am referring to (but worth looking at the whole picture)...

Code: Select all

$q .= "(CASE WHEN ".$this->db_safe ($col)." LIKE '%".$this->keyword_exact($this->db_safe ($current))."%' THEN ".$this->db_safe((count($column) - $weight))." ELSE 0 END) + <br>";
Let me know how you get on, but I will try and find a simplier/better example.

Re: MySQL Seeding ORDER BY, set absolute position in results

Posted: Fri May 15, 2009 7:21 am
by batfastad
Just tried doing something like this...

Code: Select all

SELECT *, (CASE WHEN `POSITION` IS NOT NULL THEN `POSITION` ELSE `id` END) AS `sorter` FROM `test` WHERE 1 ORDER BY `sorter` ASC LIMIT 0, 1000
But obviously it doesn't get what I'm looking for.
Is that the sort of usage you had in mind, setting up a sorter column and ordering by that?

The key is down to the THEN ... ELSE ... bit, but I can't think what to output to sorter to make this result jump ahead of a result that was previously output

This is going to get confusing! 8O :)

Re: MySQL Seeding ORDER BY, set absolute position in results

Posted: Fri May 22, 2009 7:11 am
by batfastad
Ok I don't think this is possible with MySQL but I'm going to try and track down a way of doing this programatically in PHP instead :cry: