How to select random row with count()

Question

Table names:

g_number name
123 John
123 Sara
123 Michael

I want to get one RANDOM name and quantity of all names with the same g_number in one query:

SELECT name, count(*) cnt
FROM names
WHERE g_number='123'
ORDER BY rand()

But result is always the same

name cnt
John 3

Looks like count() ignores ORDER BY rand() line. So that's the question: is it possible to solve this in ONE query?

Answer 1

I think you want to do two different things in one query:

1. randomly choose one name out of all the ones `WHERE g_number=123
2. count all the rows WHERE g_number=123.

If that's what you want to do, do it with two subqueries:

SELECT
 (
 SELECT name
 FROM t
 WHERE g_number = 123
 ORDER BY rand()
 LIMIT 1
 ) AS name
 , (
 SELECT count(*)
 FROM t
 WHERE g_number = 123
 ) AS count_of_123

That will give you as a result:

| name | count_of_123 |
|---------|--------------|
| Sara | 3 |

or

| name | count_of_123 |
|---------|--------------|
| Michael | 3 |

or

| name | count_of_123 |
|---------|--------------|
| John | 3 |

You can check it at http://sqlfiddle.com/#!9/5342d2/8/0

This is not just MySQL, it is standard SQL (except, maybe, for the name of the rand() function, that might be DBMS-dependent). The query also reflects clearly the fact that you want to do two (related but different) things. You can check also at SQLFiddle the PostgreSQL version, and the SQLite one.

---

Within the realm of MySQL, you can use one non-standard query that will also work (and which, performance-wise, is probably better):

SELECT
 name, count(*) AS count_of_123
FROM
 (
 SELECT name
 FROM t
 WHERE g_number = 123
 ORDER BY rand()
 ) AS q0 ;

I think this is closer to what the original poster had in mind. Even so, I wouldn't rely on it. This is very far from standard SQL. MySQL might decide to change how this is implementd and behaves in the future. Also, IMHO, it is less clear what the intent of the query is.

Check it at http://sqlfiddle.com/#!9/5342d2/10/0

• a_vlad: Yes! I also think about last variant! but stuck on - is it One query or Two :)
• Igor: Your last MySQL variant has the same problem as mine: it always returns the same name. The first proposed combination does the job. But i can't understand why MySQL (not yours, not mine) variant doesn't work???
• joanolo: Last variant, checked at SQLFiddle doesn't always produce the same value.
• joanolo: Press Run SQL at least ten times... not just 2 or 3.
• Igor: MySQL v5.7.11, I've clicked 20 times: result is 100% 'John', '3'

Answer 2

Another way...

SELECT SUBSTRING_INDEX(GROUP_CONCAT(name ORDER BY RAND()),
 ',', 1) AS random_name,
 COUNT(*) AS ct
 FROM names
 WHERE g_number='123';

(It assumes that there are no commas in name.)

• Igor: nice trick, but names here are just as example. in real tables may be any possible symbol.
• Rick James: @Igor - I will grant you that this is not perfect. "tab" is usually a safe character. There is a default limit of 1024 bytes on the result of GROUP_CONCAT, but that can be raised. BLOBs are likely to have every possible byte value, so it can't work there. Etc. UNHEX(substring... HEX(name) ..., avoids some of the pitfalls.