MySQL: Is it possible to return a "mixed" dataset?

Question

Hi,

I'm wondering if there's some clever way in MySQL to return a "mixed/balanced" dataset according to a specific criterion?

To illustrate, let's say that there are potential results in a table that can be of Type 1 or Type 2 (i.e. a column has a value 1 or 2 for each record). Is there a clever query that would be able to directly return results alternating between 1 and 2 in sequence:

1st record is of type 1, 2nd record is of type 2, 3rd record is of type 1, 4th record is of type 2, etc...

Apologies if the question is silly, just looking for some options. Of course, I could return any data and do this in PHP, but it does add some code.

Thanks.

Answer 1

You can use IF function as a part of your SELECT statement to change columns, but I'm not sure how to make is alternate automatically between two columns. If you however find proper condition this will work for you

SELECT IF(condition, first_column, second_column) FROM your_table

first_column and second_column can be of different type, for example:

SELECT IF(id > 10, name, status_id) FROM clients

works well when name is a VARCHAR and status_id is an INT

Answer 2

Something like this query should do:

Select some_value, x, c
From
(
  Select
    some_value, x,
    Case When x=1 Then @c1 Else @c2 End As c,
    @c1 := Case When x=1 Then @c1+2 Else @c1 End As c1,
    @c2 := Case When x=2 Then @c2+2 Else @c2 End As c2
  From test_data, (Select @c1:=0, @c2:=1) v
  Order By some_value
) sub
Order By c

It assigns unique even numbers to x=0, and odd numbers to x=1, and uses these values as sort criteria.

---

It returns

some_value  x  c
A           1  0
X           2  1
B           1  2
Y           2  3
C           1  4
Z           2  5

for the following test-data:

Create Table test_data (
  some_value VARCHAR(10),
  x  INT
);

Insert Into test_data Values('A', 1);
Insert Into test_data Values('B', 1);
Insert Into test_data Values('C', 1);
Insert Into test_data Values('Z', 2);
Insert Into test_data Values('Y', 2);
Insert Into test_data Values('X', 2);

---

Within the alternating rule values are sorted by some_value, you can change this in the inner select, or add your conditions there.

If there are more values of a certain type (1 or 2), you get them after the rest (1 2 1 2 2 2).