MariaDB 10.3.18 : How to get 2 records with random and distinct value?

Question

There is a MySQL table named stat:

line_name  device_name count
1001    3548001  2
1002    3548002  3
1003    3548003  6
2001    3548004  7
2002    3548005  3
2003    3548006  4
3001    3548007  3
3002    3548008  9
3003    3548009  7

I need to select 2 records where the first char in line_name is different For example:

1001    3548001  2
3003    3548009  7

or:

2002    3548005  3
3001    3548007  3

I tried this:

SELECT DISTINCT(SUBSTRING(line_name,1,LENGTH(line_name)-3)) as pack_id, device_name, count 
from stat
order by rand()
limit 2;

but from time to time I get the same pack_id in the result set

Also, it's obvious that this query could not return that result! — Strawberry, Sep 30 '19 at 07:14
Please provide `SHOW CREATE TABLE`. One thing I want to know is whether `line_name` is 'unique'. — Rick James, Jan 22 '20 at 21:55

Nick · Accepted Answer · 2019-09-30T08:16:48.340

3

In MariaDB 10.3 you can use ROW_NUMBER() OVER (ORDER BY RAND()) to generate a random row number for each distinct line_name and then select a random pair of values that have row number = 1:

WITH cte AS 
(SELECT *, ROW_NUMBER() OVER (PARTITION BY LEFT(line_name, 1) ORDER BY RAND()) AS rn
 FROM stat)
SELECT `line_name`, `device_name`, `count`
FROM cte
WHERE rn = 1
ORDER BY RAND()
LIMIT 2

Demo on dbfiddle

Output (for a couple of runs)

line_name   device_name count
1003        3548003     6
3002        3548008     9

line_name   device_name count
2001        3548004     7
1003        3548003     6

edited Sep 30 '19 at 08:16

answered Sep 30 '19 at 07:54

Nick

138,499
22
57
95

(I don't think the trailing `LIMIT 2` is necessary.) – Rick James Feb 10 '20 at 05:11
@RickJames given that `LEFT(line_name, 1)` could take values (based on the same data) of 1, 2 or 3, there will be 3 rows in the `CTE` which have `rn = 1`. See https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=f34f634e3036641dd1e0c87d15da9e68 – Nick Feb 10 '20 at 05:20
Cute, but don't expect performance. For that mere 9-row table, 38 Handler_read_rnd 34 Handler_read_rnd_next 21 Handler_tmp_write 9 Handler_update 10 Innodb_buffer_pool_read_requests 9 Innodb_rows_read – Rick James Jul 08 '20 at 04:57

score 1 · Answer 2 · answered Sep 30 '19 at 07:48

In MySQL 8.0, you can self-join the table in a CTE to find a random pair of records that satisfy the condition, and then use UNION ALL to unpivot the results:

WITH cte AS (
    SELECT 
        t1.line_name line_name1,
        t1.device_name device_name1,
        t1.count count1,
        t2.line_name line_name2,
        t2.device_name device_name2,
        t2.count count2
    FROM stat t1
    INNER JOIN stat t2 ON LEFT(t1.line_name, 1) != LEFT(t2.line_name, 1)
    ORDER BY RAND()
    LIMIT 1
)
SELECT line_name1, device_name1, count1 FROM cte
UNION ALL
SELECT line_name2, device_name2, count2 FROM cte

Demo on DB Fiddle:

Run #1:

| line_name1 | device_name1 | count1 |
| ---------- | ------------ | ------ |
| 3001       | 3548007      | 3      |
| 2001       | 3548004      | 7      |

Run #2:

| line_name1 | device_name1 | count1 |
| ---------- | ------------ | ------ |
| 1003       | 3548003      | 6      |
| 2002       | 3548005      | 3      |

Michel Feldheim · Answer 3 · 2019-09-30T08:11:20.197

0

You can GROUP BY pack_id and select a random of the corresponding device_name or ANY_VALUE() if your using a MySQL >= 5.7

SELECT 
  SUBSTR(line_name, 1, 1) AS pack_id,
  line_name,
  ANY_VALUE(device_name) AS device_name,
  count
FROM stat
GROUP BY pack_id
ORDER BY RAND()
LIMIT 2

Older MySQL versions

SELECT 
  SUBSTR(line_name, 1, 1) AS pack_id,
  line_name,
  device_name,
  count
FROM stat
GROUP BY pack_id
ORDER BY RAND()
LIMIT 2

http://sqlfiddle.com/#!9/2d466f2/1

Note that I have also simplified the computation of pack_id

edited Sep 30 '19 at 08:11

answered Sep 30 '19 at 07:41

Michel Feldheim

17,625
5
60
77

Though good approach, But this answer will limit the device_name to be only the max per line_name. However OP need it all to be random. – Ankit Bajpai Sep 30 '19 at 07:45

score 0 · Answer 4 · answered Sep 30 '19 at 11:31

I would simply do:

select s.*
from stat s
order by row_number() over (partition by left(line_name, 1)
                            order by rand()
                           )
limit 2;

No subqueries are needed because window functions are allowed in order by.

This may not be the most efficient method. But unless your table is large, the performance should be okay.

MariaDB 10.3.18 : How to get 2 records with random and distinct value?

4 Answers4