how to group two specific items together

Question

I have a database with questions with columns Question, Answer, Type.

Currently, this is the sql statement I am running:

SELECT Question, Answer, Type FROM goodquestions ORDER BY RAND() LIMIT 0,20

As you can see, I select random values from the table and I would like it to be that way. However, when type is 12 I would like to access the table row prior to that entry and print them out in conjunction

Like this

RANDOM
RANDOM
RANDOM
Question before 12 type
12 type question
RANDOM
RANDOM
RANDOM

It can also be like this:

Question before 12 type
12 type question
RANDOM
RANDOM
RANDOM
RANDOM
RANDOM
RANDOM

I just need them to be together and I am unable to do this right now.

if multiple 12 type questions available then how you want the order? — ramzan ali, Aug 05 '20 at 03:56
If "Question before 12 type" is selected, say, near the top, and below you select "12 type question", do you want to see "Question before 12 type" listed twice, i.e. near the top and just before "12 type question"? — kmoser, Aug 05 '20 at 04:02
Select random questions by the common way in a subquery with the limitation. Then resort them in outer query. — Akina, Aug 05 '20 at 04:25
1) How many *12 type question* exists in source data - one or many? 2) How *Question before 12 type* is determined? Type 11? Does the amount of this pre-typed questions is 1, as much as type 12, indefinite? — Akina, Aug 05 '20 at 04:28

Zhiyong · Answer 1 · 2020-08-06T02:29:41.283

I guess I see what you want. Please try this query, I changed limit 1,20 to limit 1, 10:

select @next_line_id:=0, @next_line_type:=0;
select g.*, tt.is_property
from
    (select * from
         (select *,
           rand() as rand_val,
           case
               when @next_line_type= 12 or Type = 12 then 1
               else 0
           end is_property,
           @next_line_id as next_line_id,
           @next_line_id:=id as current_id,
           @next_line_type:=Type as current_type
         from goodquestions order by id desc
         ) t
    where t.Type <> 12
    order by rand_val limit 0,10) tt
join goodquestions g on g.id = tt.id or (g.id = tt.next_line_id and tt.is_property = 1 and tt.Type <> 12)
group by g.id, g.Question, g.Answer, g.Type, tt.is_property
order by is_property desc, id
limit 0, 10;

The following are the query of creating test table:

create table goodquestions (
    id int unsigned auto_increment primary key,
    Question varchar(255) not null,
    Answer varchar(255) not null,
    Type int unsigned,
    index idx_type (Type)
) engine=innodb DEFAULT CHARSET=latin1;

insert into goodquestions (Question, Answer, Type)
values ('q1', 'a1', 1),
       ('q2', 'a2', 2),
       ('q3', 'a3', 3),
       ('q4', 'a4', 4),
       ('q5', 'a5', 5),
       ('q6', 'a6', 6),
       ('q7', 'a7', 7),
       ('q8', 'a8', 8),
       ('q9', 'a9', 9),
       ('q10', 'a10', 10),
       ('q11', 'a11', 11),
       ('q12', 'a12', 12),
       ('q13', 'a13', 13),
       ('q14', 'a14', 14),
       ('q15', 'a15', 15),
       ('q16', 'a16', 16),
       ('q17', 'a17', 17),
       ('q18', 'a18', 18);

Please note, using rand() function may have a bad performance for a large table. If there are performance issue, I could provide another solution for better performance.

The following query which result list must have and only have one record of type 12:

select @total_type_12:=(select count(*) from goodquestions where Type=12);
select @random_type_12:=(floor(rand()*@total_type_12) + 1) * 2;
select @next_line_id:=0, @next_line_type:=0, @is_property:=0;
select g.*, tt.is_property
from
    (select * from
         (select *,
           case
               when (@next_line_type= 12 or Type = 12) and @random_type_12 > 0 and @random_type_12 <= 2 then @is_property:=1
               else @is_property:=0
           end is_property,
           rand() as rand_val,
           @random_type_12 as cur_random_type_counter,
           case
               when (@next_line_type= 12 or Type = 12) and @random_type_12 > 0 then @random_type_12:=@random_type_12-1
               else @random_type_12
           end as next_rand_type_counter,
           @next_line_id as next_line_id,
           @next_line_id:=id as current_id,
           @next_line_type:=Type as current_type
         from goodquestions order by id desc
         ) t
    where t.Type <> 12
    order by is_property desc, rand_val limit 0,10) tt
join goodquestions g on g.id = tt.id or (g.id = tt.next_line_id and tt.is_property = 1 and tt.Type <> 12)
group by g.id, g.Question, g.Answer, g.Type, tt.is_property
order by is_property desc, id
limit 0, 10;

Test data set is as following:

mysql> select * from goodquestions;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    84
Current database: test

+----+----------+--------+------+
| id | Question | Answer | Type |
+----+----------+--------+------+
|  1 | q1       | a1     |    1 |
|  2 | q2       | a2     |    2 |
|  3 | q3       | a3     |    3 |
|  4 | q4       | a4     |    4 |
|  5 | q5       | a5     |    5 |
|  6 | q6       | a6     |    6 |
|  7 | q7       | a7     |    7 |
|  8 | q8       | a8     |    8 |
|  9 | q9       | a9     |    9 |
| 10 | q10      | a10    |   10 |
| 11 | q11      | a11    |   11 |
| 12 | q12      | a12    |   12 |
| 13 | q13      | a13    |   13 |
| 14 | q14      | a14    |   14 |
| 15 | q15      | a15    |   15 |
| 16 | q16      | a16    |   16 |
| 17 | q17      | a17    |   17 |
| 18 | q18      | a18    |   18 |
| 19 | q21      | a21    |   12 |
| 20 | q22      | a22    |   22 |
| 21 | q23      | a23    |   12 |
+----+----------+--------+------+
21 rows in set (0.34 sec)

[fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=274c8739f91ffb9696fe5f3b3bf84f67) with your tables and query. Execute a lot of times. If `Type = 12` is selected it returns 11 rows else 10 rows. And it does not return `Type = 12` row and its pre-row as two first ones in the rowset. — Akina, Aug 05 '20 at 05:10
And one more problem - it may return duplicated row with pre-type. And the last problem - outer query have no ORDER BY, so pre-type row immediately before type=12 row is not guaranteed. Nevertheless I vote this answer as useful. — Akina, Aug 05 '20 at 05:21
@Akina, thanks to point out those problems. please see my updates. — Zhiyong, Aug 05 '20 at 05:56
Well, but may be there is a problem - now the rest of rows is sorted by `id`. It may be safe for OP but may be not - in such case it is enough to replace `order by is_property desc, id` with `ORDER BY is_property DESC, type=12, RAND()`. — Akina, Aug 05 '20 at 08:29
@Zhiyong It didn't end up working. Here is my actual database https://filebin.net/kucdf9bizo2jz0ag — , Aug 05 '20 at 14:42
@Zhiyong 12 is not guaranteed in your version. Can you edit it so that 12 and the value of the index before the 12 value is guaranteed? — , Aug 05 '20 at 17:32
@Elizabeth, I couldn't access your link because some firewall rules in our office. Therefore I continue work on my test data set. Please see if my updates is what you want — Zhiyong, Aug 06 '20 at 02:30

Akina · Answer 2 · 2020-08-05T20:07:40.903

1

For MySQL 8+ it can be something similar to

WITH
-- SELECT 20 random rows
cte AS ( SELECT Question, Answer, Type 
         FROM goodquestions 
         ORDER BY RAND() LIMIT 0,20 )
( SELECT Question, Answer, Type
  FROM cte )
-- add pre-row if Type=12 row is selected and pre-row is not selected
UNION DISTINCT
( SELECT Question, Answer, Type
  FROM goodquestions 
  WHERE Type = 'pre-type for type 12'
  AND EXISTS ( SELECT NULL
               FROM cte
               WHERE Type = 12 ) )
-- sort placing pre-row and type=12 row at the top
ORDER BY Type = 'pre-type for type 12' DESC,
         Type = 12 DESC,
         RAND()
-- remove excess row if Type=12 row was selected in CTE
-- and pre-row was not selected in CTE but added in UNION 
LIMIT 0, 20

The query assumes that goodquestions.Type is unique.

edited Aug 05 '20 at 20:07

answered Aug 05 '20 at 05:17

Akina

39,301
5
14
25

Here is my sql table that is specific to my cause https://filebin.net/kucdf9bizo2jz0ag – Aug 05 '20 at 14:41
Static analysis: 1 errors were found during analysis. Unrecognized statement type. (near "WITH" at position 0) – Aug 05 '20 at 14:42
@Elizabeth I have already asked you in the comment - *Specify precise MySQL version*, but you ignore... *Here is my sql table that is specific to my cause* Please create a fiddle (for example, at https://dbfiddle.uk/?rdbms=mysql_8.0), insert your source data and check that it is executed without errors, then post the link. – Akina Aug 05 '20 at 16:18
10.4.11-MariaDB – Aug 05 '20 at 17:28
Zhiyongs answer worked but the value of 12 and the value before wasnt always chosen – Aug 05 '20 at 17:29
I am using Zhiyongs data table as it resembles mine. Can you use his as well and change it so that 12 is always chosen and the index's value before it is chosen as well – Aug 05 '20 at 17:31
@Elizabeth *10.4.11-MariaDB* Your version KNOWS about CTE. Your error is an issue of ancient client software which you use - it doesn't know about CTE existence, but tries to parse statements which it must transfer to MySQL doing this errorneously. Update or alter it. And again - you ignore the phrase *Please create a fiddle ...*. – Akina Aug 05 '20 at 17:49
what version should I change to – Aug 05 '20 at 19:48
I created a fiddle we can use. https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=274c8739f91ffb9696fe5f3b3bf84f67 – Aug 05 '20 at 19:51
In this fiddle, there are repetitions of the same type value sometimes and 12 does not always appear. – Aug 05 '20 at 19:53
@Elizabeth *I created a fiddle we can use.* Why you insert only the former query from Zhiyong's answer? [fiddle](https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=d764cc8e4c82697091def78cfa051e01). – Akina Aug 05 '20 at 20:07
I am a bit confused by this fiddle. Sorry I am not as experienced as you. You have three segments of sql code. Which one is the correct one – Aug 05 '20 at 20:58
@Elizabeth You see 3 queries (2 from Zhiyong's answer and 1 from my one). Test, check output validity, select more suitable for you, edit any of them if needed for to make it more safe, edit source data if needed, add your own queries, full or partial, for to look at intermediate result - it is an instrument for common online query debugging. *Which one is the correct one* Fully - none, each query has its own disadvantage. – Akina Aug 06 '20 at 04:26

how to group two specific items together

2 Answers2