1

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?

2

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

  • Yes! I also think about last variant! but stuck on - is it One query or Two :) – a_vlad Mar 5 '17 at 12:33
  • 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??? – Igor Mar 5 '17 at 12:56
  • Last variant, checked at SQLFiddle doesn't always produce the same value. – joanolo Mar 5 '17 at 13:13
  • Press Run SQL at least ten times... not just 2 or 3. – joanolo Mar 5 '17 at 13:20
  • 1
    MySQL v5.7.11, I've clicked 20 times: result is 100% 'John', '3' – Igor Mar 5 '17 at 17:11
1

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.)

  • nice trick, but names here are just as example. in real tables may be any possible symbol. – Igor Mar 6 '17 at 8:14
  • @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. – Rick James Mar 6 '17 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.