MySQL Combine Multiple Queries

Question

Here is a sample table.

ColZero | ColOne | RoColTwo
--------+--------+--------
   1    | one    | tag1
   2    | two    | tag1
   3    | three  | tag2
   4    | four   | tag2
   5    | five   | tag3

I have the following query.

SELECT `ColOne` FROM `Table` WHERE `ColTwo` = ? ORDER BY rand() LIMIT 1;

I'd like to select multiple rows, using multiple values of RowTwo in a single query but each row still has to be random.

This involves merging a variable number of instances of this query into a new, single query.

How can this be done in one query? (I am trying to make this as quick as possible, so getting it all done in one request, as opposed to 10-30 requests is a big plus.)

Are you confusing the terms "Row" and "Col"?

Rick James
– Rick James

2016-10-26 23:39:34 +00:00
Commented Oct 26, 2016 at 23:39 — Rick James
– Rick James, Commented Oct 26, 2016 at 23:39

Gordon Linoff · Accepted Answer · 2016-10-26 17:45:59Z

2

Probably the most efficient way to do what you want is to use variables:

SELECT t.*
FROM (SELECT t.*,
             (@rn := if(@r = RowTwo, @rn + 1,
                        if(@r := RowTow, 1, 1)
                       )
             ) as rn
      FROM `Table` t CROSS JOIN
           (SELECDT @r := '', @rn := 0) params
      WHERE `RowTwo` IN (?, ?, ?)
      ORDER BY RowTwo, rand()
     ) t
WHERE rn = 1;

If you don't want to use dynamic SQL and you want to support an indefinite number of values, you might consider:

WHERE FIND_IN_SET(RowTwo, ?) > 0

In any case, naming columns ROW causes (for me at least) cognitive dissonance.

answered Oct 26, 2016 at 17:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lucien Over a year ago

Ty! Oops, fixed the naming. I'm struggling to understand some of this. What goes in place of params?

Gordon Linoff Over a year ago

I'm not sure what you mean. The ? are the values that you want to check RowTwo for. You can leave out that where clause to get a random row for each value.

ScaisEdge · Accepted Answer · 2016-10-26 17:45:48Z

1

You could use union using different value for RowTo

SELECT `RowOne` FROM `Table` WHERE `RowTwo` = ? ORDER BY rand() LIMIT 1

union 

SELECT `RowOne` FROM `Table` WHERE `RowTwo` = ? ORDER BY rand() LIMIT 1

union 

SELECT `RowOne` FROM `Table` WHERE `RowTwo` = ? ORDER BY rand() LIMIT 1

union 

SELECT `RowOne` FROM `Table` WHERE `RowTwo` = ? ORDER BY rand() LIMIT 1 ;

answered Oct 26, 2016 at 17:45

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

2 Comments

Lucien Over a year ago

Ty! The only problem is that forces the selection of a fixed number of rows.

ScaisEdge Over a year ago

yIn someway yes .. you can only change change the limit and the number of union ..

Collectives™ on Stack Overflow

MySQL Combine Multiple Queries

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related