11

It's possible get a random value of the group by?

----------------
 nID |  val
---------------
  A  |   XXX
  A  |   YYY
  B  |   L
  B  |   M
  B  |   N
  B  |   P
----------------

With this SQL:

SELECT nID, VAL FROM T1 GROUP BY nID

My result always is:

nID  val
--------
A    XXX
B    L

But i want a diferent result of evey nID. Like:

nID  val
--------
A    YYY
B    N

or

nID  val
--------
A    XXX
B    P

It's possible?

http://sqlfiddle.com/#!2/357b8/3

Taryn
  • 242,637
  • 56
  • 362
  • 405
Enzo
  • 4,111
  • 4
  • 21
  • 33
  • 2
    Try it and see. What is this? "Surprise me! Send back whatever you want!" How does this fit into an API? – duffymo Oct 04 '12 at 10:34
  • You'd be better placed learning to use standardised SQL rather than MySQL's appalling implementation of group by – podiluska Oct 04 '12 at 10:38

6 Answers6

3

Use a sub-query.

SELECT r.nID,
(SELECT r1.val FROM T1 r1 WHERE r.nID=r1.nID ORDER BY rand() LIMIT 1) AS 'val' FROM T1 r 
GROUP BY r.nID

http://sqlfiddle.com/#!2/357b8/18

noj
  • 6,740
  • 1
  • 25
  • 30
3

You can use order by rand() then group by them.

Like

  SELECT nID, VAL FROM (
  SELECT nID, VAL
  FROM T1
  ORDER BY RAND()
  )AS subquery
GROUP BY nID
Sanuj
  • 1,077
  • 1
  • 11
  • 23
  • A subquery is a relation, and relations in SQL have no inherent order. In that sense, the `ORDER BY` line in your query has no well-defined meaning, and future versions of MySQL might simply ignore that line, without violating any guarantees that I know of. So even while this works *now*, there is no guarantee that this will still work in the future. – MvG Oct 04 '12 at 11:05
  • Can you please explain it a bit more?? Should it be done in different way (is there?) if higher versions of MySQL are going to ignore the `ORDER BY` clause? – Sanuj Oct 04 '12 at 11:47
  • The approach suggested by xdazz and jonnyynnoj is not affected by this: it uses the `ORDER BY RAND()` in a query which also has a `LIMIT`, so the order has a semantic effect and won't be optimized away. Your version might have better performance now, though. Apparently you can't have both performance and guarantees. This question here just inspired me to ask [this question](http://stackoverflow.com/q/12726549) which might or might not provide more alternatives. – MvG Oct 04 '12 at 11:52
  • 1
    @MvG Play to the landscape. Predicting the future is not being useful. This will be **way** faster than the other two. – RichardTheKiwi Oct 04 '12 at 12:59
  • 1
    So here is the future: I have MySQL 5.7.17 and this code is not working anymore. I always get the same non-random results. – vandroid Mar 22 '17 at 15:18
1
SELECT 
  t1.nID, 
  (SELECT 
     t2.var 
   FROM your_table t2 
   WHERE t1.nID = t2.nID ORDER BY rand() LIMIT 1
  ) AS var 
FROM your_table t1 
GROUP BY t1.nID ;
xdazz
  • 158,678
  • 38
  • 247
  • 274
0

Try This

SELECT nID, VAL
FROM (select nID, VAL from T1 order by rand()) as T
group by nID
Mohit Mehta
  • 1,283
  • 12
  • 21
  • The [comment](http://stackoverflow.com/questions/12725363/group-by-value-rand/12725537#comment17186874_12725537) I wrote to Sanuj's answer applies to yours as well. – MvG Oct 04 '12 at 11:54
0

The following solution is similar in spirit to those from xdazz or jonnyynnoj. But instead of SELECT FROM T1 GROUP BY nID I use a subquery to select all distinct IDs. I believe there is a chance that the performance might differ, so give this one a try as well.

SELECT nID,
  (SELECT VAL
   FROM T1
   WHERE T1.nID = ids.nID
   ORDER BY RAND()
   LIMIT 1
  ) AS VAL
FROM (SELECT DISTINCT nID FROM T1) AS ids
Community
  • 1
  • 1
MvG
  • 57,380
  • 22
  • 148
  • 276
0

rand + rownum

SELECT t.*
, @rownum := @rownum+1 AS rowNum
FROM(
  SELECT nID, VAL 
  FROM T1 
  ORDER BY RAND()
) AS t, (SELECT @rownum :=0) AS R
GROUP BY nID
ORDER BY nID, rowNum
Dayul Kwak
  • 1
  • 1