CODEWITHSUNDEEP
mysql
NaNuk
NaNuk

Reputation: 141

mySQL create a result set of two table alternating rows

I have a query that uses union to join two sub queries e.g.

SELECT * FROM posts WHERE postTypeId=1 (e.g. blog)
UNION
SELECT * FROM posts WHERE postTypeId=2 (e.g. news)

The result set that this approach generates positions the two sub-sets sequentially ("blog" then "news").

I want to create a a result set which interleaves the two, alternating between rows from the "blog" sub-set and the "news" sub-set.

I feel that there must be a simple way to do this, but I have failed to find it.

Any suggestions would be greatly appreciated.

Nick

Upvotes: 2

Views: 1795

Answers (4)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Try

Set @current_row = 0
SELECT * FROM (
  SELECT @current_row := @current_row + 1 as Position, posts.*
    FROM posts
    WHERE postTypeId=1
  UNION 
  SELECT @current_row, posts.*
    FROM posts
    WHERE postTypeId=2
) dummyTableName
ORDER BY position, postTypeId

maybe

Upvotes: 2

NaNuk
NaNuk

Reputation: 141

This is solution that best works for me. It's not identical to any of the current proposals, so I have added it independently. @a, @b and @c are used to create row numbers per sub-set, meaning that in the combined results, 3 rows will share the same row number (a "row set"). This is used as the first order sort, and second order sort then orders the rows within the "row set". 

SET @a = 0;
SET @b = 0;
SET @c = 0; 

SELECT * FROM(
  SELECT @a := @a + 1 AS sortOne, 1 AS sortTwo, posts.* FROM posts WHERE posts.postTypeId=3
    UNION
  SELECT @b := @b + 1 AS sortOne, 2 AS sortTwo, posts.* FROM posts WHERE posts.postTypeId=2
    UNION
  SELECT @c := @c + 1 AS sortOne, 3 AS sortTwo, posts.* FROM posts WHERE posts.postTypeId=1
) AS result ORDER BY sortOne, sortTwo

This solutions is derived/inspired by submitted solutions, but I don't think it appropriate to mark any of them as being an accepted solution in itself. So, credit where it's due to Thomas Clayson, Tony Hopkinson and rmunoz whose answers I've voted up. Cheers!

Upvotes: 4

arutaku
arutaku

Reputation: 6107

That's easy, you can add a parameter in your subquery, the parameter "rank", this way:

SET @rank=0;
SELECT @rank:=@rank+2 AS rank FROM posts WHERE postTypeId=1 (e.g. blog)

Then, you get:

0, ...
2, ...

If you do the same in the other query but init initializating rank to 1, you will get:

1, ...
3, ...

Finally "ORDER BY rank" and you will get posts and news mixed.

Upvotes: 2

Thomas Clayson
Thomas Clayson

Reputation: 29935

Well, here is a novel way I can think of (not tested, but you'll get the gist):

SELECT * FROM (
    SELECT 1 AS query, @n := @n + 1 AS rowNumber, posts.* FROM (select @n:=0), posts WHERE posts.postTypeId=1
    UNION
    SELECT 2 AS query, @n := @n + 1 AS rowNumber, posts.* FROM (select @n:=0), posts WHERE posts.postTypeId=2
) ORDER BY rowNumber, query;

So this will do the two queries and then order by first rowNumber and then by query. What you'll end up with is something like:

rowNumber | query
1         | 1
1         | 2
2         | 1
2         | 2
etc...

SELECT @n=:0 resets the global variable n to 0 for the query and then the @n := @n + 1 increments the value for each row.

If you need any more explanation let me know. I hope this works! :)

Upvotes: 2

Related Questions