0

Assuming that we have the following MySQL table:

ID | Name | Last_Name | Location  |
1  | Alex | Griff     | DT        |
2  | John | Doe       | York      |
3  | Pat  | Benat     | DT        |
4  | Jack | Darny     | DT        |
5  | Duff | Hill      | York      |

I want to create an sql statement that selects randomly one row of each location and store them in a new table.

For example:

2  | John | Doe       | York      |
3  | Pat  | Benat     | DT        |

OR

4  | Jack | Darny     | DT        |
5  | Duff | Hill      | York      |

I would like to execute this on SQL since it's much faster than doing it on a Java program and using HashMap<K,V> and then storing the values again in another table.

Alan Deep
  • 2,037
  • 1
  • 14
  • 22
  • See this question - http://stackoverflow.com/questions/249301/simple-random-samples-from-a-sql-database – Tom Ron May 22 '14 at 16:00
  • @TomRon . . . The questions are quite different, because this question is about a random sample from each location, rather than an overall random sample. – Gordon Linoff May 22 '14 at 16:17

2 Answers2

0

If you want a random sample for each location, you have several options. I think the easiest is a variable approach that will work well if your table is not super big.

select t.*
from (select t.*,
             @rn := if(@location = location, @rn + 1, 1) as rn,
             @location := location
      from table t cross join
           (select @location := '', @rn := 0) vars
      order by location, rand()
     ) t
where rn = 1;

This assigns a sequential number to the locations and then chooses the first one.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

This page has the solution. I simply modified the query to your table definition, as follows:

SELECT tmp.ID, tmp.Name, tmp.Last_Name, tmp.Location
FROM profiles
LEFT JOIN (SELECT * FROM profiles ORDER BY RAND()) tmp ON (profiles.Location = tmp.Location)
GROUP BY tmp.Location
ORDER BY profiles.Location;

SQL Fiddle demo

Joseph B
  • 5,519
  • 1
  • 15
  • 19
  • This request assumes that the values for `tmp` all come from the same record. In fact, the MySQL documentation *specifically* warns against this use of group by: http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html. – Gordon Linoff May 22 '14 at 19:32