Alternating rows based on a value of a column in MySQL

Question

I have a problem with selecting alternating rows in mysql - Image:

CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `file` varchar(255) DEFAULT NULL,
  `project_id` int(10) unsigned DEFAULT NULL,
);

And want to be able to select rows from images table in such a way as to have alternating values of project_id IS NOT NULL. Basically:

• image with project_id
• image without project_id
• image with project_id
• image without project_id
• image with project_id
• image without project_id

How can this be accomplished in one SQL query? I've tried:

SELECT `images`.*, 
IF(images.project_id, images.project_id % 2, images.id % 2) AS `mixer`
FROM `images` AS `images`
ORDER BY `mixer` DESC

But it's not what I need. Also the final query will probably have a "GROUP BY images.id" in it so if the solution does not mess up the query that would be quite welcome.

Answer 1

Interesting challenge. Try this:

SELECT images.*, 
FROM images, (SELECT @x:=-1, @y:=0) c
ORDER BY CASE WHEN project_id is not null THEN @x:=@x+2 ELSE @y:=@y+2 END;

This works by incrementing one of two values @x and @y based on the condition and having the value you want ordered first start with a lower initial value. The result of the evaluation is in the range 1, 2, 3, 4, ... as can be seen from running:

SELECT CASE WHEN project_id is not null THEN @x:=@x+2 ELSE @y:=@y+2 END z, images.*, 
FROM images, (SELECT @x:=-1, @y:=0) c
ORDER BY z;